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).

 

[alansidman]

 

[Monty85]

Are you saying you want to consolidate 12 files with 20 sheets into one sheet? Am I understanding you correctly?

Correct. I can manually do it, but was wondering if there's a shortcut.

So at the moment from what I understand I can follow the steps to consolidate each files sheets into a single file. Then follow the same steps again to consolidate my new files into a single file.

Not the end of the world if not - i think the above method isn't too time consuming considering the amount of files I have but always good to know if there are more efficient methods.

 

[alansidman]

Look at the link in post #11. I think you will find this will do what you want with little effort. Also, any updates to the source files will automatically be updated in PQ when you select Refresh All on the Data Tab. that is where the big savings are. Additionally, if you add files to the sub forum they will also be included in the update.

 

[Monty85]

Look at the link in post #11. I think you will find this will do what you want with little effort. Also, any updates to the source files will automatically be updated in PQ when you select Refresh All on the Data Tab. that is where the big savings are. Additionally, if you add files to the sub forum they will also be included in the update.

Thank you.

One final question - which is an issue i've come across during testing.

Most of my data uses columns B through to I but occasionally i'm coming across a file that has a value in Column A.

When this happens, it still consolidates everything but the columns don't align anymore. For example, the sheets with data in Columns A-I look correct, but the sheets with data in Columns B-I are being presented moved across to the left (i.e. the data ends up in Columns A-H.

I hope that makes sense.

Is there a something I do to mitigate this through the Power Query options or am I out of luck?

 

[alansidman]

If the column headings are exactly the same for all columns, the PQ will align the data correctly. PQ is sensitive to capitalization, ie. A does not equal a. Be sure that all headings are consistent from ws to ws. It does not make a difference where the columns are located but spelling is what counts. Be sure all column headings are consistently spelled the same.

 

[Monty85]

If the column headings are exactly the same for all columns, the PQ will align the data correctly. PQ is sensitive to capitalization, ie. A does not equal a. Be sure that all headings are consistent from ws to ws. It does not make a difference where the columns are located but spelling is what counts. Be sure all column headings are consistently spelled the same.

Ah ok. This data only populates a heading in Column A when there is data in it - normally its just a blank column, so thats where its going wrong.

I just need it to ignore the headings and just copy them down based on there position only. Maybe I need to look into the macro option posted earlier.

 

[alansidman]

What you are saying should not make a difference. Without seeing your data, this is the best I can do. If there is no data to merge then there is no data to merge.

 

[Monty85]

What you are saying should not make a difference. Without seeing your data, this is the best I can do. If there is no data to merge then there is no data to merge.

Yeah im not sure what is happening. I tried setting up an example of the issue but it just worked as it should when I tested it.

Then tested a single file again and that looked like it worked ok but then issue popped back up when i did the multiple files.

So I might just need to resort to transforming the data file by file so I can validate each one as it gets set up.

 

[Monty85]

What you are saying should not make a difference. Without seeing your data, this is the best I can do. If there is no data to merge then there is no data to merge.

I've saved a file i have and redacted all the information and the issue is being repeated now using the power query merge step. It might be something to do with Column A being formatted/hidden in some odd way on some of the files (im not sure). But the issue at least is being repeated on this example.

Hopefully there's a simple solution here that i've missed....

Here's the file - hopefully its ok to share this way (not sure what the best method is)

Sep_2020_redacted_sample.xlsx

20200903 Effective Date,Valuation Date 20200903,20200901,1,REDACTED_1,REDACTED_1,999,999,999 20200903,20200901,1,REDACTED_2,REDACTED_2,999,999,999 20200903,20200901,1,REDACTED_3,REDACTED_3,999,999,999 20200903,20200901,1,REDACTED_4,REDACTED_4,999,999,999 20200903,20200901,1,REDACTED_5,REDACTED_5...

docs.google.com

 

[alansidman]

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