Combining multiple sheets with mostly similar columns - VBA

[juxel]

I am really struggling trying to combine multiple sheets into one. The first ~80 or so columns are always the same, but then there can be some variation in the next.

Sheet 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]US[/TD]
[TD]Africa[/TD]
[TD]Asia[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]Bob[/TD]
[TD]34[/TD]
[TD]32[/TD]
[TD]29[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]John[/TD]
[TD]91[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]47[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]US[/TD]
[TD]Middle East[/TD]
[TD]Asia[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]83[/TD]
[TD]Carl[/TD]
[TD]23[/TD]
[TD]93[/TD]
[TD]74[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]Jill[/TD]
[TD]57[/TD]
[TD]92[/TD]
[TD]74[/TD]
[TD]23[/TD]
[/TR]
</tbody>[/TABLE]


I want to combine Sheet 1 and Sheet 2 into a single view and I only care about the columns in Sheet 1, so the end result would look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]US[/TD]
[TD]Africa[/TD]
[TD]Asia[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]Bob[/TD]
[TD]34[/TD]
[TD]32[/TD]
[TD]29[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]John[/TD]
[TD]91[/TD]
[TD]20[/TD]
[TD]18[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD]83[/TD]
[TD]Carl[/TD]
[TD]23[/TD]
[TD][/TD]
[TD]74[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]Jill[/TD]
[TD]57[/TD]
[TD][/TD]
[TD]74[/TD]
[TD]23[/TD]
[/TR]
</tbody>[/TABLE]


Complicating the matter is that I have multiple files I want to apply this to and the first sheet will have variable numbers of columns and there will be variable sheets to combine in each file. Some files have 2, some have 12+.

The closest I've gotten is to copy over the columns that are static in all sheets (ID and Name in the simplified example) and then do a vlookup with match like

=VLOOKUP(A3, Table2[#All],MATCH(C1,Table2[#Headers],0),0)

This approach seems to accomplish the task, but requires a lot of manipulation and lots of changing of the formula for sheets after 2.

My file is much more complicated than this, but it doesn't appear I can upload a sample.

Appreciate any help you can give.

 

[alansidman]

This can be accomplished quickly and easily using the Power Query functionality of Excel. This is available for download in versions 2007, 2010, 2013 if you don't already have it. It is included in version 2016. Simply load the tables and then merge them and delete any columns you don't need.


https://www.powerquery.training/portfolio/merge-tables/