Bets way to consolidate multiple sheets containing raw data

[Monty85]

As the title hints, I am looking for a solution to merge the data contained in multiple sheets into a single sheet. The data itself is formatted identically in every sheet but isn't converted to a range or table.

I've tried using the "Consolidate" function in the Data menu but that would only sum the amounts together and not stack the data down the page.

Through some searches I found the solution using Power Query but as my data isn't formatted as a table by default thats not really an option (as if i need to go into every sheet to do that I may as well just copy and paste the data).

 

[Monty85]

Well, I see your issue to start with. You don't have column headers for each column. This is critical. For Column A, if there is data, then it needs to have a column header. As I said earlier, PQ appends data based upon the column header. No header and you have a mess. In you example column A does not contain any data. In the link below, I have added headers for each column except A. I then appended each of them to create one file.

Sample excel file.zip | Powered by Box

app.box.com

Thanks, I was afraid of that.

I don't think there's any practical way for me to add headers into the sheets though (there's just too many files for this to be efficient).

 

[alansidman]

Then I guess, you will have to find an alternative solution. Good Luck. I've given you my best.

 

[Monty85]

Then I guess, you will have to find an alternative solution. Good Luck. I've given you my best.

It's a strange one - other files in the set work ok (and they have the same headers situation).

Thanks for your help anyway, much appreciated - i'll keep at it.

 

[Trebor76]

Have you tried my macro solution? I just added a new sheet called Sheet1, ran the macro and it and it consolidated all of other 21 tabs.