Forum

Notifications
Clear all

Automatic correspondence in an Excel table

3 Posts
2 Users
0 Reactions
114 Views
(@seb_infovba)
Posts: 2
New Member
Topic starter
 

Hello,

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.

 
Posted : 01/10/2021 12:17 pm
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Hi Sebastien,

The quickest solution would be to use the SUBSTITUTE function

=SUBSTITUTE(SUBSTITUTE(H3," ", " / "),"License","O365 ")

See attached file.

Regards

Phil

 
Posted : 01/10/2021 8:58 pm
(@seb_infovba)
Posts: 2
New Member
Topic starter
 

Hi Philip,

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.

 
Posted : 04/10/2021 9:08 am
Share: