Combining worksheets with different header columns in an Excel file

Dan_var

Board Regular
Joined
Jan 22, 2009
Messages
92
Hello,

I am trying to combine the following 3 work sheets in an excel file : The combined file should have the headers as in Sheet 1. So sheets 2 and 3 should be appended to Sheet 1. I need to define which columns in Sheets and 2 and 3 should be appended to which columns in Sheet1. Is there a way to do this using Power Query or any other simple way ? The videos that show the Power Query are bit confusing.
sheet 1REGIONCountry CodeUIDEMP IDNAMEUSD
sheet 2REGIONCountryIDNAMELCAMOUNT
sheet 3REGIONCountryIDEMPLCAMOUNT

Many a thanks
Regards
Dan
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
you can try with Table.FromRecords
Power Query:
let
    TFR2 = Table.FromRecords({[REGION = "REGION", Country = "Country Code", ID = "UID", NAME = "EMP ID", LC = "NAME", AMOUNT = "USD"]}),
    TFR3 = Table.FromRecords({[REGION = "REGION", Country = "Country Code", ID = "UID", EMP = "EMP ID", LC = "NAME", AMOUNT = "USD"]}),
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    App2 = Table.Combine({TFR2, Source2}),
    Prom2 = Table.PromoteHeaders(App2, [PromoteAllScalars=true]),
    Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    App3 = Table.Combine({TFR3, Source3}),
    Prom3 = Table.PromoteHeaders(App3, [PromoteAllScalars=true]),
    Append = Table.Combine({Source1, Prom2, Prom3})
in
    Append
 
Upvote 0
Thank you so much. I'll try your method, the actual columns are lot more. Appreciate your prompt response,

regards
 
Upvote 0
You are welcome
next time post representative example using XL2BB
update your profile about Excel version and OS
have a nice day
 
Upvote 0
Hello, thanks again for your time,
I created the three tables 1,2,3 and put in your query also. The Result - output is what I would like. For example the Country should bring in Country code, Amount usd from other files etc.
RegionCountryIDNameLCAmountusdcodetable1
RegionCountry CodeUIDEmp IDNameusdcurr codetable2
na
1​
xyzzzzzztough
15​
usd
RegionCountry CodeUIDEmp IDNameInputusdcodetable3
emea
40​
xyzzzzzzgood
30​
40​
ukp
Result
RegionCountryIDNameLCAmountusdcodeoutput
na
1​
xyztough
15​
15​
usd
emea
40​
xyzgood
30​
40​
40​
ukp

But I got the following after the Append step. It is not bringing the date under correct columns and also creates additional columns like Column 1 etc.. For instance UID is coming under Column 8 and not under ID, same for USD, any ideas where am I missing ? Regards, Dan
Column1Column2Column3Column4Column5Column6Column7REGIONCountry CodeUIDEMP IDNAMEUSDColumn8Column9Column10Column11Column12
RegionCountryIDNameLCAmountUSD
RegionCountry CodeUIDEmp IDNameusd
na
1​
xyzzzzzztough
15​
na
1​
abcaaaaeasy
25​
RegionCountry CodeUIDEmp IDNameInput
emea
1​
xyzzzzzzgood
30​
emea
1​
abcaaaaokay
40
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top