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 D...
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 names are predefined such way, back to the sample
you may generate the spill by formula as
=REDUCE(Table1[#Headers], "Table" & SEQUENCE(6), LAMBDA(a,v, VSTACK(a,INDIRECT(v) ) ) ), the only in your case it'll be 188 instead of 6.
Or return structured table by Power Query with 4 steps. Generated script is
let Source = Excel.CurrentWorkbook(), #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Table")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}), #"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"A", "B"}, {"A", "B"}) in #"Expanded Content"In general no coding is required, practically all could be done from UI.
