consolidate multiple (188) tables, all formatted the same, into a single worksheet. The tables are A

[Ray_Nord]

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

[#2]

https://answers.microsoft.com/en-us/msoffice/forum/all/combine-3-documents/0960df89-09c9-44c7-9555-4...

[#3]

@Ray_Nord 

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.

[#4]

Many thanks I will try it now
Ray

[#5]

@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!

[#6]

@SanthoshKunder 

VSTACK() assumes some logic in tables location, not only the same structure; or we have to pick-up somehow all 188 table names.

[#7]

@Sergei Baklan 

The tables are simply named "table 1, table 2, table 3" all the way to table 188

[#8]

@Ray_Nord 

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.

[#9]

@Sergei Baklan We can use 3D reference since tables are structurally same.

[#10]

@SanthoshKunder 

Perhaps, but we don't know details how exactly tables are located. Let assume all 188 tables are in only one sheet, or in different sheets but locations are different? The only we know for now all sheets have the same structure, i.e. same number of columns and same column names. And they are structured tables, not ranges.