Macro to Merge tables 1:M relationship

[Gimics]

Hello,

I am looking for some help to combine two tables of separate data based on a single column with a 1:M relationship. I do not have power pivot, so I am defaulting to vba as the best option (unless I hear otherwise).

Example:

The two tables share Group as a common Key - the macro should create a new table, joining both on group and extending to include both values. I do have other columns of data in each table, but they would be simple additions to the result and do not affect the join.

Table 1

Account	Group
50000	Apple
50000	Plum
50000	Pear
60000	Apple
60000	Plum
60000	Pear

<tbody>
</tbody>

Table 2

Group	Center
Apple	1000
Apple	2000
Apple	3000
Plum	2000
Plum	2500
Plum	3000
Pear	1000
Pear	2500
Pear	3000

<tbody>
</tbody>

Expected result:

Account	Group	Center
50000	Apple	1000
50000	Apple	2000
50000	Apple	3000
50000	Plum	2000
50000	Plum	2500
50000	Plum	3000
50000	Pear	1000
50000	Pear	2500
50000	Pear	3000
60000	Apple	1000
60000	Apple	3000
60000	Apple	2000
60000	Plum	2000
60000	Plum	2500
60000	Plum	3000
60000	Pear	1000
60000	Pear	2500
60000	Pear	3000

<tbody>
</tbody>

Thanks in advance!

 

[Rick Rothstein]

Are your tables real Excel Table objects or just ranges of cells that you think of as a table? If the former, are the table names really Table1 and Table2? If the latter, what is the addresses of your two table ranges?

 

[Gimics]

Are your tables real Excel Table objects or just ranges of cells that you think of as a table? If the former, are the table names really Table1 and Table2? If the latter, what is the addresses of your two table ranges?

Hey Rick - they are real table objects with table names "Table1" and "Table2". If it's easier to use the range addresses, they both start at 1,1 of two separate worksheets (Sheet1 and Sheet2).

Thanks

 

[Gimics]

Anyone with any insights? I was thinking of using "for" loops with 'find' lookups and storing the found ranges then cycling through everything, for each item in the original table... but there's gotta be a simpler way!