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
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Plum[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Plum[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Pear[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Center[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]Plum[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]Plum[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]Plum[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]3000[/TD]
[/TR]
</tbody>[/TABLE]


Expected result:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Group[/TD]
[TD]Center[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Apple[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Apple[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Apple[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Plum[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Plum[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Plum[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Pear[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Pear[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]Pear[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Apple[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Apple[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Apple[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Plum[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Plum[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Plum[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Pear[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Pear[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]60000[/TD]
[TD]Pear[/TD]
[TD]3000[/TD]
[/TR]
</tbody>[/TABLE]


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!