How to merge two tables with multiple instances of a key?

[sanguine35711]

Is this possible?  I have two excel documents, file 1: "hostnames with component type" and "hostnames with serial numbers" with a common column title "Machine Name (Required)" which I believe I can use as the key between the two tables.  But in one of the tables I have multiple instances of the Machine Name value because these represent machines with mulitple IP Addresses.  

The end result would be to have a third table (or excel document) with the combined information from both excel documents in which the data the corresponsding to a specific hostname (all the row data) is inserted from "hostnames with serial numbers" into "hostnames with componenty type".

This is the excel table named ""hostnames with componenty type"

This is the excel table named "hostnames with serial numbers"

Please help!

[v-yangliu-msft · Accepted solution]

Hi  @sanguine35711 ,

You can add an Index to the two tables respectively.

Through Merge, use the [Index] of the two tables as a common column for Left outer.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.