2

I have two Excel documents with AD user information that I need to merge to gather all information.

One of the documents has information about AD groups and the names of the members of these groups. The other document has information about the users - if they are enabled, username, name and e-mail address.

The common values in the documents are the names. I want the values of the users' usernames and e-mail addresses over in the Excel document with the groups.

Example document Groups

Example document users

I have more than 3400 rows in the groups document and 271 users listed in the users document. In the groups document the users will be listed several times, since a user can, and will be a member of more than one group.

Is there a way to merge the data I want (e-mail address and username) using the common value in both the documents (the users name)? And is there any problem with the users being listed several times?

Soltuion

I managed to do this by installing an addon to Excel, Ablebits Data.

https://www.ablebits.com/

It gave me the possibility to merge the excel documents and add data fra one to the other with reference to the data that was the same.

1

I would use the fully integrated Get & Transform Excel feature, since you have Excel 2016. For previous Version you can use the free MS Power Query Add-in.

Combine data from multiple data sources is without programming/vba knowledge not difficult at all. The MS Support describe it here step-by-step.

You need this main steps.

  1. Import the first sourceenter image description here
  2. Import the second sourceenter image description here
  3. Combine the first source to the second one through definition of common columns/fields.enter image description here

Here the M-Code (you don't have to write it actually, when you use the user Interface of Get & Transform):

  • First Source (e.g. user's Information)

    let
        Source = Excel.Workbook(File.Contents("C:\yourPath\example\Input2.xlsx"), null, true),
        Table13_Table = Source{[Item="Table13",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Table13_Table,{{"Name", type text}, {"Enabled", type logical}, {"email", type text}})
    in
        #"Changed Type"     
    
  • Second Source (e.g. AD groups), where the 1st source is combined with

    let
        Source = Excel.Workbook(File.Contents("C:\Users\EDubosson\Documents\Privat\OneDrive\Öffentlich\example\Input1.xlsx"), null, true),
        Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"GroupName", type text}, {"Member", type text}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Member"},Table13,{"Name"},"Table13",JoinKind.Inner),
        #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Table13", {"Enabled", "email"}, {"Enabled", "email"})
    in
        #"Expanded {0}"
    

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.