Help filtering a table using LET, UNIQUE functions

[hcova]

Hi everyone.
1) I have the following excel table.
The Table es named Table1

2) I need to write a Dynamic Array formula to calculate the sum of TOTAL (col K) for each unique Code (Col G).
Something like the following output table

During my process to get the above table, I tried a way to get the results, but without the table I needed.
Here my steps I did

3) I applied the following formula in cell B37 and I got the below table

4) Then I wrote the following formula to get the totals by code

to obtain this table

5) This table solved my problem, but I could't get the output table that I needed as mentioned in 2).

To get the output table of 2), can someone please help me modify the Dynamic Array formula LET/UNIQUE/LAMBDA indicated in 4)

Thanks a lot any help

 

[Dave Patton]

It will be very helpful if you post your example with the forum's tool named XL2BB.

With Excel 365, try Groupby

 

[hcova]

Filter Demo.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Item	ITEM DESCRIPTION	SubItem	SubItem Description	Basis Number	Code	DESCRIPTION 2	QUANT	U.P.	TOTAL
2		1	Previous buildings			1.1	410160	Container for Office	6.00	7.50	45.00
3		1	Previous buildings			1.2	410160	Warehouse Container	6.00	4.90	29.40
4		1	Previous buildings			1.3	410160	WC Chemical	6.00	3.21	19.28
5		1	Previous buildings			1.4	410160	Container shipping	4.00	7.59	30.36
6		1	Previous buildings			1.5	410160	Pre closes	50.00	0.95	47.50
7		1	Previous buildings			1.6	410160	Electric y water provision	6.00	8.04	48.21
8		2	Pre work			2.1	410106	Rethinking plans	1.00	15.00	15.00
9		2	Pre work			2.2	410106	Drawings and levels	1.00	26.00	26.00
10		3	soil movement			3.1	410150	Masiva digging for improvements	932.00	0.45	419.40
11		3	soil movement			3.2	410150	Fundations digging	104.22	0.52	54.19
12		3	soil movement			3.3	410130	Improvement stuffings	932.00	0.65	605.80
13		3	soil movement			3.4	410130	Geogrilla Stratagrid BX30	621.00	0.09	55.89
14		3	soil movement			3.5	410150	Excedent removement	1,036.22	0.56	580.28
15		4	Fundations			4.1	410163	Concrete Emplant	9.87	2.17	21.42
16		4	Fundations			4.2	410194	Reforce steel A63-42H Fund. and Bars	5,934.00	0.05	296.70
17		4	Fundations			4.3	410163	Concrete basement G25	102.80	2.85	292.98
18		4	Fundations			4.4	410151	Mold Fundaciones Plac terciad	345.00	0.45	155.25
19		5	Slab			5.1	410194	Steel for Reforcement A63-42H	0.00	0.00	0.00
20		5	Slab			5.2	410163	Concrete G25	40.38	3.14	126.79
21		5	Slab			5.3	410151	Wall Mold	404.00	0.62	250.48
22		5	Slab	5.1	Floor raft foundation	5.1.1	410132	Compact seal.	305.00	0.10	28.98
23		5	Slab	5.1	Floor raft foundation	5.1.2	410132	Ripio layer	30.50	0.62	18.83
24		5	Slab	5.1	Floor raft foundation	5.1.3	410132	Grid acma c192	1,110.00	0.08	88.80
25		5	Slab	5.1	Floor raft foundation	5.1.4	410132	polyethylene	305.00	0.05	15.25
26		5	Slab	5.1	Floor raft foundation	5.1.5	410163	Concrete raft fundation G20 12cm	305.00	0.68	207.40
27		6	Column for Beams			6.1	410196	Metallic beam	1,286.00	0.11	135.95
28		6	Column for Beams			6.2	410240	Base plaque enchap. structural 18mm	70.92	0.48	34.04
29		6	Column for Beams			6.3	410240	Acustic Bandage fonodan	204.00	0.07	13.67
30		7	Fire Protection			7.1	410144	Fire Retardant anti termit AF-7000 fire proteccion	70.92	0.08	5.32
31		7	Fire Protection			7.2	410144	Bar polyethylene 0,20mm	70.92	0.05	3.55
32		8	OverSlab			8.1	410163	OverSlab Concrete heavy 50mm	70.92	0.57	40.44
33
34
35
36
37	Extract Colums before Sum	Code	TOTAL
38		410106	15.00
39		410106	26.00
40		410130	605.80
41		410130	55.89
42		410132	28.98
43		410132	18.83
44		410132	88.80
45		410132	15.25
46		410144	5.32
47		410144	3.55
48		410150	419.40
49		410150	54.19
50		410150	580.28
51		410151	155.25
52		410151	250.48
53		410160	45.00
54		410160	29.40
55		410160	19.28
56		410160	30.36
57		410160	47.50
58		410160	48.21
59		410163	21.42
60		410163	292.98
61		410163	126.79
62		410163	207.40
63		410163	40.44
64		410194	296.70
65		410194	0.00
66		410196	135.95
67		410240	34.04
68		410240	13.67
69
70	DYNAMIC ARRAY SOLUTION
71	Sum using LET & UNIQUE	Code	TOTAL
72		410106	41.00
73		410130	661.69
74		410132	151.85
75		410144	8.87
76		410150	1,053.88
77		410151	405.73
78		410160	219.75
79		410163	689.03
80		410194	296.70
81		410196	135.95
82		410240	47.71
83
84
85	I need this OUTPUT
86
87		Item	ITEM DESCRIPTION	SubItem	SubItem Description	Basis Number	Code	DESCRIPTION 2	QUANT	U.P.	TOTAL
88		----------	----------	----------	----------	----------	410106	----------	----------	----------	41.00
89		----------	----------	----------	----------	----------	410130	----------	----------	----------	661.69
90		----------	----------	----------	----------	----------	410132	----------	----------	----------	151.85
91		----------	----------	----------	----------	----------	410144	----------	----------	----------	8.87
92		----------	----------	----------	----------	----------	410150	----------	----------	----------	1,053.88
93		----------	----------	----------	----------	----------	410151	----------	----------	----------	405.73
94		----------	----------	----------	----------	----------	410160	----------	----------	----------	219.75
95		----------	----------	----------	----------	----------	410163	----------	----------	----------	689.03
96		----------	----------	----------	----------	----------	410194	----------	----------	----------	296.70
97		----------	----------	----------	----------	----------	410196	----------	----------	----------	135.95
98		----------	----------	----------	----------	----------	410240	----------	----------	----------	47.71
99
100
Table

Cell Formulas
Range	Formula
B37:C68	B37	=CHOOSECOLS(VSTACK(Table1[#Headers],SORT(FILTER(Table1,Table1[Code],"NO DATA"),6,1)),6,10)
B71:C82	B71	=VSTACK(B37:C37,LET( uniqueCodes, UNIQUE(B38:B68),incomeTotal, MAP(uniqueCodes, LAMBDA(code, SUMIFS(C38:C68, B38:B68, code))),HSTACK(uniqueCodes, incomeTotal)))
G88:G98	G88	=+B72
K88:K98	K88	=+C72
Dynamic array formulas.

 

[Dave Patton]

Did you try the suggestion?

Groupby_a.xlsx
	A						G				K	L	M	N
1	A						CODE				TOTAL
2							410160				10.00		410155	1,350.00
3							410160				15.00		410160	175.00
4							410160				20.00		410170	275.00
5							410160				25.00		Total	1,800.00
6							410160				30.00
7							410160				35.00
8							410160				40.00
9							410170				45.00
10							410170				50.00
11							410170				55.00
12							410170				60.00
13							410170				65.00
14							410155				70.00
15							410155				250.00
16							410155				280.00
17							410155				350.00
18							410155				400.00
19
2f

Cell Formulas
Range	Formula
M2:N5	M2	=GROUPBY(Table5[CODE],Table5[TOTAL],SUM)
Dynamic array formulas.

 

[Dave Patton]

Groupby_a.xlsx
	B	C
35	410106	41.00
36	410130	661.69
37	410132	151.85
38	410144	8.87
39	410150	1,053.88
40	410151	405.73
41	410160	219.75
42	410163	689.03
43	410194	296.70
44	410196	135.95
45	410240	47.71
46	Total	3,712.16
47
48
Sheet2

Cell Formulas
Range	Formula
B35:C46	B35	=GROUPBY(Table6[Code],Table6[TOTAL],SUM)
Dynamic array formulas.

 

[Fluff]

How about

=LET(s,REPT("-",10),g,GROUPBY(Table1[Code],Table1[TOTAL],SUM,0,0),r,ROWS(g),VSTACK(Table1[#Headers],HSTACK(EXPAND(s,r,5,s),TAKE(g,,1),EXPAND(s,r,3,s),TAKE(g,,-1))))

or if you don't have groupby yet

=LET(s,REPT("-",10),u,UNIQUE(Table1[Code]),c,SUMIFS(Table1[TOTAL],Table1[Code],u),r,ROWS(u),VSTACK(Table1[#Headers],HSTACK(EXPAND(s,r,5,s),u,EXPAND(s,r,3,s),c)))

 

[Dave Patton]

If you require a report with a Grand Total, Total by Code, and Totals with full descriptions, Excel has a feature that does this.
You do not enter any formulas; it yields three views.

Look at Data | Outline | Subtotal