consolidation of two sheets with partially different column headers

[SabrinaCon]

Hello MrExcel community,

I've been looking for a solution of my issue and couldn't find any coherent help.

I have two sheets which have specific names. Let's say "sheet 1" and "sheet 2".
How can I consolidate the data per VBA in a newly created sheet if sheet 1 and sheet 2 have columns that have headers with same names but also have headers which are different?
The consolidation sheet would then have all columns of sheet 1 and 2 without listing duplicate column headers. In database terms: The consolidation sheet would be a full join of the two sheets with empty cells where there is no match.


Big thanks in advance!



Sabrina

 

[Fazza]

hi, Sabrina

Excel can behave like a database, treating each worksheet as a table, & do a full join. No VBA required.

First save the file. Then ALT-D-D-N to start the query wizard, choosing Excel files, browse & select your file. You'll get a message about no visible tables if there are no simple (non-dynamic) named ranges that can be data sources. This won't appear again if you select options and then system tables. After this you'll also see worksheet names. Select either (or even both) "sheet 1" or "sheet 2" and follow the wizard to the end. Then choose the option to edit in MS Query. Now in the GUI, like in MS Access, you can add the second table if you don't already have it & create a join between the tables. Or directly edit the SQL to get what you want. When done you'll see the results dataset & you can return this to a worksheet as a query table. It is refreshable like a pivot table and has some handy properties. Suggest you google for more info - Excel query tables.

cheers

PS. If you want specific help on the SQL, please post details such as sample input tables with headers & data, and the desired output table with the correct/matching output data.

 

[SabrinaCon]

Hi Fazza,

thanks for your reply.

I followed your instructions but I don't know which type of join I have to create and how.




The consolidation sheet should look like this resulting from the sheet 1 and 2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD]def[/TD]
[TD][/TD]
[TD][/TD]
[TD]ghi[/TD]
[TD]jkl[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]mno[/TD]
[TD]pqr[/TD]
[TD][/TD]
[TD][/TD]
[TD]stu[/TD]
[TD]vwx[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD]ccc[/TD]
[TD]ddd[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]eee[/TD]
[TD]fff[/TD]
[TD]ggg[/TD]
[TD]hhh[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



sheet 1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]abc[/TD]
[TD]def[/TD]
[TD]ghi[/TD]
[TD]jkl[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]mno[/TD]
[TD]pqr[/TD]
[TD]stu[/TD]
[TD]vwx[/TD]
[/TR]
</tbody>[/TABLE]



sheet 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD]ccc[/TD]
[TD]ddd[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]eee[/TD]
[TD]fff[/TD]
[TD]ggg[/TD]
[TD]hhh[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

Great that you got into MS Query. There is a button there 'SQL'. Just edit the text to make it the SQL you need. Looks like a simple UNION query.

SELECT ID, A, B, Null AS [C], Null AS [D], X, Y
FROM [Sheet 1$]
UNION ALL
SELECT ID, A, B, C, D, Null, Null
FROM [Sheet 2$]

 

[SabrinaCon]

Big thanks Fazza!

Is there actually a way of making a statement in SQL that sets all columns except from specific ones as NULL?


Sabrina

 

[Fazza]

hello, Sabrina

I don't understand what is wanted. An example, or more words of explanation, is needed.

Likely what is wanted is simple enough via VBA. Just need to understand what is wanted. The VBA can interrogate the spreadsheet, determine the headers on each worksheet and then, if you can explain what is wanted, the SQL can be created - it is just text - and then assigned to the query. If it is existing - or create it if it is new. Then refresh the query to have the result. There might be a little bit of detail to know how to reference the query table as it changed a few years ago. I don't recall when. Maybe Excel 2007. It used to be just a query table and then became a query object or something..

regards, Fazza