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

[frnorke]

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 corresponding to a specific hostname (all the row data) is inserted from "hostnames with serial numbers" into "hostnames with componentry type".



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

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

Please help!

 

[KRice]

Welcome! Sure, one of the easiest ways to do this is Power Query. Are you interested in having the results table also show multiple MAC and/or IP addresses on the same row in separate columns, or perhaps combining the sets into single cells separated with a delimiter? What would the structure of the results table look like?

 

[frnorke]

Welcome! Sure, one of the easiest ways to do this is Power Query. Are you interested in having the results table also show multiple MAC and/or IP addresses on the same row in separate columns, or perhaps combining the sets into single cells separated with a delimiter? What would the structure of the results table look like?

Hello. Thank you for the quick reply. Multiple instances of the 'Machine Name (Required)' values (where the value appears more than once in that column) are fine. I understand that for some of the row data, there will be blanks. I can work on getting that data from MECM at a later point.

 

[frnorke]

Hello. Thank you for the quick reply. Multiple instances of the 'Machine Name (Required)' values (where the value appears more than once in that column) are fine. I understand that for some of the row data, there will be blanks. I can work on getting that data from MECM at a later point.

I'm sorry, I mean that a

 

[frnorke]

I'm sorry, I mean that a

I don't know how to delete a mistake in this forum. That previous post was a mistake. I meant to say that having a 'Machine Name (Required' value that has multiple IP addresses, one per row, is fine.

 

[KRice]

Below your post, you should have an edit button to modify a recent post. "Recent" meaning that you have only 10 minutes to edit a post before the option goes away. Then for any must-have edits, you would need to talk with a moderator.

1. So the two source tables...are they in separate workbooks or does each exist on a different sheet in the same workbook?
2. It appears that the structure (column headings in this case) of the two source tables is almost the same, but not quite? Is the only difference that one has a column [Model] while the other has [Model Number]? Are those fields actually the same? I'm trying to understand if one could overlay the two tables and have the column names and order match?
3. Do you want to aggregate all of the columns in the results table?...and if I understand your last comment about "one per row is fine", that means Table2 (the one with duplicated hostnames) will have some number of rows, and the results table would then have the same number of rows (unless Table 1 includes entries for which there is no corresponding Table2 match)?

 

[frnorke]

Below your post, you should have an edit button to modify a recent post. "Recent" meaning that you have only 10 minutes to edit a post before the option goes away. Then for any must-have edits, you would need to talk with a moderator.

1. So the two source tables...are they in separate workbooks or does each exist on a different sheet in the same workbook?
2. It appears that the structure (column headings in this case) of the two source tables is almost the same, but not quite? Is the only difference that one has a column [Model] while the other has [Model Number]? Are those fields actually the same? I'm trying to understand if one could overlay the two tables and have the column names and order match?
3. Do you want to aggregate all of the columns in the results table?...and if I understand your last comment about "one per row is fine", that means Table2 (the one with duplicated hostnames) will have some number of rows, and the results table would then have the same number of rows (unless Table 1 includes entries for which there is no corresponding Table2 match)?

Hi, yes the two sources are in separate workbooks and the two columns you referred too should have the same names. That was a mistake on my part. One could overlay the two tables and have the columns name and order match. For number 3, yes that is correct. The resultant table would have all of the rows in table 1 with the corresponding row data from table 2 where the host names match even if there are duplicate hostnames in table 2 because there are different IP addresses due to multiple virtualized Network interface cards.