Dynamic table? maybe?

[ExcelQA]

Hi everyone, I have little bit of a challenge. I developed a model for a colleague who is very used to "his format". Is there a way to create a dynamic (non pivot table) to automatically transform the data into one with the format below? the challenge is that for example on the table below the level 0 and level 2 changes month over month

 

[duggie33]

Maybe something like this would work...you pick Level 0 from the dropdown in H1 and see the results. The potential problem with stacking results as shown is that if there are more or fewer rows under any Level 0, other results should shift up or down. I also noticed that SWE and US do not have totals.

Book_2023-10-03.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Level 2	Level 1	Level 0	Total		RU		US
2	Mig29	ABC	RU	1,000		UK			Baloon	100
3	SU35	ABC	RU	1,700		SWE			F22	3,000
4	Tornado	DCB	UK	2,000		US			V22	800
5	Typhoon	DCB	UK	2,500					F16	1,000
6	Viggen	XCY	SWE	1,000					Total:	4,900
7	Grippen	XCY	SWE	1,700
8	Baloon	DEF	US	100
9	F22	DEF	US	3,000
10	V22	DEF	US	800
11	F16	DEF	US	1,000
Sheet7

Cell Formulas
Range	Formula
F1:F4	F1	=UNIQUE(C2:C11)
I2:J6	I2	=LET(filt,FILTER(CHOOSE({1,2},A2:A11,D2:D11),C2:C11=H1),b,HSTACK("Total:",SUM(INDEX(filt,,2))),VSTACK(filt,b))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H1	List	=$F$1#

Hope that helps,

Doug

 

[Georgiboy]

Maybe the below code will give you the layout you need, i have assumed that the range you supplied was A1:D11:

Sub test()
    Dim rng As Range, var As Variant
    Dim uLevel0 As Variant, x As Long, z As Long, a As Long
    Dim oVar As Variant
    Dim oRng As Range, rCell As Range
    
    Set rng = Range("A2:D" & Range("A" & Rows.Count).End(xlUp).Row)
    var = rng.Value
    
    With Application
        uLevel0 = .Unique(.Index(rng, , 3))
        ReDim oVar(.CountA(.Index(rng, , 3)) + UBound(uLevel0) * 2, 2)
        For x = 1 To UBound(uLevel0)
            oVar(z, 0) = uLevel0(x, 1): z = z + 1
            For a = 1 To UBound(var)
                If var(a, 3) = uLevel0(x, 1) Then
                    oVar(z, 1) = var(a, 1)
                    oVar(z, 2) = Format(var(a, 4), "0,0")
                    z = z + 1
                End If
            Next a
            oVar(z, 2) = Format(.SumIf(.Index(rng, , 3), uLevel0(x, 1), .Index(rng, , 4)), "0,0")
            z = z + 1
        Next x
        Set oRng = Range("G2").Resize(UBound(oVar) + 1, 3)
        oRng = oVar
        For Each rCell In .Index(oRng, , 1)
            If rCell.Value <> vbNullString Then
                With rCell.Font
                    .Color = 10384908
                    .Bold = True
                End With
            ElseIf rCell.Offset(, 2) <> vbNullString And rCell.Offset(, 1) = vbNullString Then
                With rCell.Offset(, 2).Font
                    .Bold = True
                End With
            End If
        Next rCell
    End With
End Sub

 

[alansidman]

Deleted.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Level 2	Level 1	Level 0	Total
2	Mig29	ABC	RU	1000		RU
3	SU35	ABC	RU	1700			Mig29	1000
4	Tornado	DCB	UK	2000			SU35	1700
5	Typhoon	DCB	UK	2500				2700
6	Viggen	XCY	SWE	1000		UK
7	Grippen	XCY	SWE	1700			Tornado	2000
8	Baloon	DEF	US	100			Typhoon	2500
9	F22	DEF	US	3000				4500
10	V22	DEF	US	800		SWE
11	F16	DEF	US	1000			Viggen	1000
12							Grippen	1700
13								2700
14						US
15							Baloon	100
16							F22	3000
17							V22	800
18							F16	1000
19								4900
20
Sheet3

Cell Formulas
Range	Formula
F2:H19	F2	=LET(u,UNIQUE(FILTER(C2:C100,C2:C100<>"")),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,VSTACK(EXPAND(y,,3,""),IFNA(HSTACK("",FILTER(CHOOSECOLS(A2:D100,1,4),C2:C100=y)),"")),CHOOSE({1,2,3},"","",SUMIFS(D:D,C:C,y))))),1))
Dynamic array formulas.

 

[Fluff]

Realised I had an extra vstack which is not need, so updated formula

=LET(u,UNIQUE(FILTER(C2:C100,C2:C100<>"")),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,EXPAND(y,,3,""),IFNA(HSTACK("",FILTER(CHOOSECOLS(A2:D100,1,4),C2:C100=y)),""),CHOOSE({1,2,3},"","",SUMIFS(D:D,C:C,y))))),1))

 

[ExcelQA]

Realised I had an extra vstack which is not need, so updated formula

=LET(u,UNIQUE(FILTER(C2:C100,C2:C100<>"")),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,EXPAND(y,,3,""),IFNA(HSTACK("",FILTER(CHOOSECOLS(A2:D100,1,4),C2:C100=y)),""),CHOOSE({1,2,3},"","",SUMIFS(D:D,C:C,y))))),1))

I love this solution! thank you i think this does the trick...

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ExcelQA]

Thank you and sorry for the newbie question... I've never user Stacks.. what if I want to add more columns, somehow i cant get the totals to work

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Level 2	Level 1	Level 0	Total
2	Mig29	ABC	RU	1000	397	889			RU
3	SU35	ABC	RU	1700	617	224				Mig29	1000	397	889
4	Tornado	DCB	UK	2000	909	784				SU35	1700	617	224
5	Typhoon	DCB	UK	2500	892	852					2700	1014	1113
6	Viggen	XCY	SWE	1000	367	497			UK
7	Grippen	XCY	SWE	1700	598	866				Tornado	2000	909	784
8	Baloon	DEF	US	100	314	317				Typhoon	2500	892	852
9	F22	DEF	US	3000	937	501					4500	1801	1636
10	V22	DEF	US	800	442	502			SWE
11	F16	DEF	US	1000	390	885				Viggen	1000	367	497
12										Grippen	1700	598	866
13											2700	965	1363
14									US
15										Baloon	100	314	317
16										F22	3000	937	501
17										V22	800	442	502
18										F16	1000	390	885
19											4900	2083	2205
Sheet3

Cell Formulas
Range	Formula
I2:M19	I2	=LET(u,UNIQUE(FILTER(C2:C100,C2:C100<>"")),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,VSTACK(EXPAND(y,,5,""),IFNA(HSTACK("",FILTER(CHOOSECOLS(A2:F100,1,4,5,6),C2:C100=y)),"")),CHOOSE({1,2,3,4,5},"","",SUMIFS(D:D,C:C,y),SUMIFS(E:E,C:C,y),SUMIFS(F:F,C:C,y))))),1))
Dynamic array formulas.