Forum Discussion
consolidate multiple (188) tables, all formatted the same, into a single worksheet. The tables are A
I have a file that I want to consolidate into a single worksheet for purposes of analysis. It is in the form of 188 tables all formatted the same and of consistent size. The tables are columns A to DL and each is rows 8 to 59.
Can you suggest a formula or process to create the single sheet?
Ray
Ray_Nord -My two cents
- Power query is an ideal option!
- VSTACK formula also does the job here. Of course you need to adjust the range and sheet name.
=VSTACK(A1:A1,'S1:S4'!A8:DL59)- You can do it with the help of Command Prompt too!
VSTACK() assumes some logic in tables location, not only the same structure; or we have to pick-up somehow all 188 table names.
The tables are simply named "table 1, table 2, table 3" all the way to table 188
If we speak about structured tables perhaps Power Query is the easiest way. Start with blank query, add = Excel.CurrentWorkbook()
as the first step, if necessary uncheck other than 188 table names and expand the content.
- Many thanks I will try it now
Ray
