Create single list from multiple worksheets

[jb]

Hello Helpers,
I have multiple sheets each having 3 types of list. Refer Group_1, Group_2 and Group_3 sample sheets of testing excel file attached here.

Row number 1 to 10 is reserved for CEO position entry. Row 1 is title and 2 to 10 is for names.
Row number 11 to 20 is reserved for Secretary position entry. Row 11 is title and 12 to 20 is for names.
Row number 21 to 100 is reserved for Member position entry. Row 21 is title and 22 to 100 is for names.

Now, I want to automatically generate consolidated sheet as per the format given in "Consolidated" sheet. I have typed manually.
But actually, data in group sheets keeps on changing. So every time manually generating consolidated sheet is very tedious task.

Kindly help. Formula based answer would be fine. If not possible, VBA will also do.

Thanks.

 

[AlanSidman]

Here is a VBA solution. I opened a blank excel sheet and then imported the file into PQ. Be sure to change the path to reflect where your file is located.

let
    Source = Excel.Workbook(File.Contents("C:\Users\alans\Downloads\testing.xlsx"), null, true),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Group_1", "Group"}, {"Column2", "Position"}, {"CEO", "Name"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Position] <> null) and ([Group] <> "consolidated"))
in
    #"Filtered Rows"

 

[pecoflyer]

Here is a VBA solution

I don't quite understand Alan, where is the VBA here?

 

[Peter Bartholomew]

I don't quite understand Alan, where is the VBA here?

I think it is a case of "where you see VBA, read PQ"

With 365, the starting point is

= VSTACK(CEO, secretaries, members)

 

[AlanSidman]

Thanks Peter. Yes. Should be Power Query. My Bad.

 

[p45cal]

Academic only, single cell formula in cell J2:

or shorter:

=LET(a,VSTACK(Group_1:Group_3!A1:H30),FILTER(IF(a="","",a),INDEX(a,,3)<>""))

 

[jb]

Thanks for help.
Just for information can you explain what is VSTACK?

 

[p45cal]

Just for information can you explain what is VSTACK?

VSTACK Function

The Excel VSTACK function combines arrays vertically into a single array. Each subsequent array is appended to the bottom of the previous array.

exceljet.net

 

[Peter Bartholomew]

The key feature of @p45cal's solution is that VSTACK (and some other array shaping helper functions) work with 3D ranges, combining them to form a single 2D range.