How to merge multiple sheets with each table headers start from second row

[jaden5165]

Hi,

May I learn how to merge multiple sheets with each table header starting from the second row in each sheet into a single master sheet that can view all data at a glance.
I have tried googled and learned about using power query to actually merge multiple sheets into a single sheet. However, I encountered an issue due to my table data actually start from the 2nd row in each individual sheet, when I merge these sheets by using power query editor, The header names will become Column1, Column2,... and so on instead of the real table header names that I would like to see. I believe this is due to my table data not starting from 1st row in each sheet.

Or there is other better solutions can merge all the sheets into one ?

Example, I got 3 individual sheets as shown in the pictures below:

Sheet A, Sheet B, Sheet C
Picture1.png

The desire end result would be something like the picture below:
Sheet MASTER
Untitled (3).png

Currently facing issue with the Expanded Data not showing correct headers name, only show Column1, Column 2 ....
Untitled (4).png

Would like to have something like this with correct header names,
Untitled (5).png

I have also uploaded my workbook here. Thank you

[Glenn Kennedy]

1. Please confirm that you are siill using Excel 2013. This is very easy with the latest (beta) version of O365.

2. Please confirm how many sheets you wish to merge in your real data.

[jaden5165]

1. Please confirm that you are siill using Excel 2013. This is very easy with the latest (beta) version of O365.

2. Please confirm how many sheets you wish to merge in your real data.

Hi Glenn, I am using Office365. There are about 15 sheets to be merged into single Master sheet.

[Glenn Kennedy]

1. Please change your profile NOW to show O365.

2. Have you got version 2205 (File/Account/About Excel)?

3. If not can you subscribe to Office Insider (File/Account/Office Insider) and update to the latest version?

4. If all that is possible, then you can use VSTACK in one easy-to-use formula:

=VSTACK(A!A2:J12,B!A3:J5,'C'!A3:J22)

[jaden5165]

1. Please change your profile NOW to show O365.

2. Have you got version 2205 (File/Account/About Excel)?

3. If not can you subscribe to Office Insider (File/Account/Office Insider) and update to the latest version?

4. If all that is possible, then you can use VSTACK in one easy-to-use formula:

=VSTACK(A!A2:J12,B!A3:J5,'C'!A3:J22)

Hi Glenn, I have changed my Excel version in my profile. Unfortunately, I cannot get the Office Insider as I am currently using School version provided by my class that has not got the Office Insider to get the latest 2205 version. I am still using 2203 version.

Can you teach other alternative solution that is feasible for 2203 version?

Thank you.

[JeteMc]

You could use power query, provided the tables are all defined to start with cell A2 instead of A1.
In the attached workbook the ranges on sheets A, B and C are converted into tables and then connections are made to those tables using power query.
The PQ tables are then appended, and, after the fact, the numeric values are changed to percentage from decimal.
Let us know if you have any questions.

[jaden5165]

You could use power query, provided the tables are all defined to start with cell A2 instead of A1.
In the attached workbook the ranges on sheets A, B and C are converted into tables and then connections are made to those tables using power query.
The PQ tables are then appended, and, after the fact, the numeric values are changed to percentage from decimal.
Let us know if you have any questions.

Hi JeteMC, ya the sheets A,B,C will always start with A2.
May I know the steps to turn the sheets A,B,C into table and create the connections into Master?

Thank you.

[JeteMc]

1. Select any cell in the data range on the A sheet and press the Ctrl + t keys
2. Edit the $A$1:$J$13 in the Create Table dialog box to read: $A$2:$J$13
3. Press OK
4. Repeat steps 1:3 for the data ranges on the B:C sheets.
The linked video tutorial explains how to make connections to the three tables and then append them into a single table: https://www.youtube.com/watch?v=oeQPtbFNYIw
Let us know if you have any questions.

[jaden5165]

Hi JeteMC,

I managed to follow the above steps to get the result I want. Thank you very much.

Thank Glenn and JeteMC solution sharing. My issue resolve.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.