Compiling and then splitting an array (array formula or VBA)

[Matrovsky]

So I have a table of widgets and the layers of material needed to make them:

Type	Layer 1	Layer 2	Layer 3	Layer 4	Layer 5	Layer 6	Layer 7
Type1	1.a
Type2	2.a
Type3	3.a	3.b
Type4	4.a	4.b	4.c	4.d	4.e
Type5	5.a	5.b	5.c	5.d
Type6	6.a	6.b	6.c

I need to transpose these values into something like this:

Type1		Type4		Type5
1.a		4.a		5.a
		4.b		5.b
Type2		4.c		5.c
2.a		4.d		5.d
		4.e
Type3				Type6
3.a				6.a
3.b				6.b
				6.c

I already have an awkward function to do this, but it requires manually adjusting the size of each HSTACK element to generate (roughly) equal columns (FirstGroup, SecondGroup, and ThirdGroup are the manually-determined lengths of each column):

=LET(bigarray,
  VSTACK(
    LET(CurrEntry,1,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0)),"",
    LET(CurrEntry,2,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0)),"",
    LET(CurrEntry,3,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0)),"",
    LET(CurrEntry,4,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0)),"",
    LET(CurrEntry,5,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0)),"",
    LET(CurrEntry,6,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0)),"",
    LET(CurrEntry,7,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0)),"",
    LET(CurrEntry,8,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0)),"",
    LET(CurrEntry,9,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0)),"",
    LET(CurrEntry,10,CurrFilt,IFERROR(CHOOSECOLS(TRANSPOSE(tbl_Sample),CurrEntry),""),FILTER(CurrFilt,CurrFilt<>0))
        ),
  IFERROR(
    LET(FirstGroup,9,
        SecondGroup,7,
        ThirdGroup,10,
        HSTACK(
          CHOOSEROWS(bigarray,SEQUENCE(FirstGroup)),"",
          CHOOSEROWS(bigarray,SEQUENCE(SecondGroup,,FirstGroup+2)),"",
          CHOOSEROWS(bigarray,SEQUENCE(ThirdGroup,,FirstGroup+SecondGroup+2))
              )
        )
        ,"")
  )

I know there's an automated way to do this but I'm just not finding it. Can anyone help?

(I'd also rather have a dynamic VSTACK(TRANSPOSE(...)) option instead of making entries for each row of the table, but that's secondary.)

It doesn't have to be an array formula - the whole sheet will be generated with VBA so a code option is fine too.

 

[alansidman]

Will this presentation work for you?

Book1
	A	B	C	D	E	F	G	H
1	Type	Layer 1	Layer 2	Layer 3	Layer 4	Layer 5	Layer 6	Layer 7
2	Type1	1.a
3	Type2	2.a
4	Type3	3.a	3.b
5	Type4	4.a	4.b	4.c	4.d	4.e
6	Type5	5.a	5.b	5.c	5.d
7	Type6	6.a	6.b	6.c
8
9	Type1	Type2	Type3	Type4	Type5	Type6
10	1.a	2.a	3.a	4.a	5.a	6.a
11			3.b	4.b	5.b	6.b
12				4.c	5.c	6.c
13				4.d	5.d
14				4.e
Sheet1

If the answer is yes, then Power Query is the answer

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Type"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Type]), "Type", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute"})
in
    #"Removed Columns"

 

[Matrovsky]

Will this presentation work for you?

...

Unfortunately, no. The end result is part of an order form with a fixed format that can't be altered. It has to be three columns, ideally with the results spread equally across them. Thank you for the suggestion though.

 

[Eric W]

Here's a VBA option. Not my cleanest code, but it works:

Sub StackTypes()
Dim Table As Range, Output As Range
Dim nr As Long, cnt() As Long, i As Long, j As Long
Dim b As Long, c As Long, an As Long, bn As Long, cn As Long
Dim m As Long, mm As Long, ixb As Long, ixc As Long
Dim r As Long, x As Long

    Set Table = Range("A2:H7")
    Set Output = Range("A10")
    
    nr = Table.Rows.Count
    ReDim cnta(1 To nr)
    For i = 1 To nr
        cnta(i) = WorksheetFunction.CountA(Table.Offset(i - 1).Resize(1))
    Next i
    
    mm = WorksheetFunction.Sum(cnta)
    
        For b = 2 To nr - 1
            For c = b + 1 To nr
                an = 0
                For i = 1 To b - 1
                    an = an + cnta(i)
                Next i
                bn = 0
                For i = b To c - 1
                    bn = bn + cnta(i)
                Next i
                cn = 0
                For i = c To nr
                    cn = cn + cnta(i)
                Next i
                m = WorksheetFunction.Max(an, bn, cn) - WorksheetFunction.Min(an, bn, cn)
                If m < mm Then
                    mm = m
                    ixb = b
                    ixc = c
                End If
            Next c
        Next b
    
    c = 0
    r = 0
    
    For i = 1 To ixb - 1
        x = 0
        While Table.Offset(i - 1, x).Resize(1, 1) <> ""
            Output.Offset(r, c) = Table.Offset(i - 1, x).Resize(1, 1)
            x = x + 1
            r = r + 1
        Wend
        r = r + 1
    Next i
    
    c = c + 2
    r = 0
    For i = ixb To ixc - 1
        x = 0
        While Table.Offset(i - 1, x).Resize(1, 1) <> ""
            Output.Offset(r, c) = Table.Offset(i - 1, x).Resize(1, 1)
            x = x + 1
            r = r + 1
        Wend
        r = r + 1
    Next i
    
    c = c + 2
    r = 0
    For i = ixc To nr
        x = 0
        While Table.Offset(i - 1, x).Resize(1, 1) <> ""
            Output.Offset(r, c) = Table.Offset(i - 1, x).Resize(1, 1)
            x = x + 1
            r = r + 1
        Wend
        r = r + 1
    Next i
    
End Sub

Change the first 2 indented lines to the input and output ranges you want. This is the result of that macro:

Book1
	A	B	C	D	E	F	G	H
1	Type	Layer 1	Layer 2	Layer 3	Layer 4	Layer 5	Layer 6	Layer 7
2	Type1	1.a
3	Type2	2.a
4	Type3	3.a	3.b
5	Type4	4.a	4.b	4.c	4.d	4.e
6	Type5	5.a	5.b	5.c	5.d
7	Type6	6.a	6.b	6.c
8
9
10	Type1		Type4		Type5
11	1.a		4.a		5.a
12			4.b		5.b
13	Type2		4.c		5.c
14	2.a		4.d		5.d
15			4.e
16	Type3				Type6
17	3.a				6.a
18	3.b				6.b
19					6.c
Sheet3