September 29, 2021
I'm regularly asked to export user accounts from an Active Directory using a PowerShell script,
which also allows me to obtain the Office 365 licenses assigned to each of these user accounts.
This therefore created 2 CSV files, the 1st containing the user accounts and the 2nd the Office 365 licenses.
To format these files very quickly, I created a specific Excel file for them.
In the second CSV, the licenses obtained through the script are written with different words compared to what I have on my portal
Microsoft Office 365 Administration.
For example: ENTERPRISEPACK (license via script) = Office 365 E3 (on my Office 365 portal).
To match the licenses, I therefore added a sheet in my Excel file in which I have a table
that I fill in manually from the beginning, and it forces me to take into account all possible combinations
in case there are users who can have several different licenses assigned.
I have attached to this topic an example Excel file with 2 sheets:
- the first, named CURRENTLY, corresponds to what I described above.
- the second, named WISH, corresponds to what I want to do, the subject of my request, the question of which is below.
Is it possible to return the correspondence automatically with a formula or other feature of Excel,
using a reference table listing only each license?
For clarification, I use the Office 365 version of Excel.
October 5, 2010
September 29, 2021
Thanks a lot for your help.
But I encounter a difficulty. My apologies, I forgot an important detail.
In total, I have 26 Office 365 licenses, each with a different name, which can stand out in my CSV file.
Therefore, do I have as many SUBSTITUTE nested as I need? Or is there some other feature, or even some VBA code that would make this task easier for me?
Thank you in advance for your help.