Merge data in two excel files

Question

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.

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.

Answer 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 source
2. Import the second source
3. Combine the first source to the second one through definition of common columns/fields.

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}"