Alternate to pivot table

[sharshra]

I have the following table & I want to generate a summary. This can be easily done using pivot tables. But, I would like to use lambda & related helper functions to achieve the same. At present, I´m getting the output with formula copied to each cell. I would like to avoid this & write formula in a single cell. The formula should get the data, header & the numbers. I´m faltering in getting the correct lambda function for this. Can the experts in this forum help please?

Source table:

excel problems.xlsx
	B	C	D	E
2	id	module	name	result
3	1	C	dfd	pass
4	2	C	cvcfdf	fail
5	3	L	wertew	fail
6	4	L	r	pass
7	5	L	qew	no result
8	6	L	ioi	pass
9	7	C	h	pass
10	8	C	u	pass
11	9	C	ioi	fail
12	10	L	wewq	pass
13	11	L	cvxz	fail
14	12	L	hjg	fail
15	13	L	ew	no result
16	14	L	yui	pass
17	15	C	cx	no result
18	16	C	ad	pass
19	17	C	gd	fail
20	18	L	jgj	pass
21	19	L	kj	fail
22	20	L	xz	pass
23	21	C	ñk	fail
24	22	C	kh	pass
25	23	C	hf	pass
26	24	C	gd	fail
27	25	L	fs	pass
Sheet1

Summary table:

excel problems.xlsx
	G	H	I	J	K
2	module	count of names	pass	fail	no result
3	C	12	6	5	1
4	L	13	7	4	2
Sheet1

Cell Formulas
Range	Formula
H3:H4	H3	=LET( tId, Table1[id], m, Table1[module], COUNT(UNIQUE(FILTER(tId,m=G3))))
I3:K4	I3	=LET( tId, Table1[id], m, Table1[module], r, Table1[result], ROWS(FILTER(r,(m=$G3)*(r=I$2))))

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		id	module	name	result		module	count of names	pass	fail	no result
3		1	C	dfd	pass		C	11	6	5	1
4		2	C	cvcfdf	fail		L	13	7	4	2
5		3	L	wertew	fail
6		4	L	r	pass
7		5	L	qew	no result
8		6	L	ioi	pass
9		7	C	h	pass
10		8	C	u	pass
11		9	C	ioi	fail
12		10	L	wewq	pass
13		11	L	cvxz	fail
14		12	L	hjg	fail
15		13	L	ew	no result
16		14	L	yui	pass
17		15	C	cx	no result
18		16	C	ad	pass
19		17	C	gd	fail
20		18	L	jgj	pass
21		19	L	kj	fail
22		20	L	xz	pass
23		21	C	ñk	fail
24		22	C	kh	pass
25		23	C	hf	pass
26		24	C	gd	fail
27		25	L	fs	pass
Data

Cell Formulas
Range	Formula
G3:K4	G3	=LET(u,UNIQUE(Table1[module]),r,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,ROWS(UNIQUE(FILTER(Table1[name],Table1[module]=y)))))),1),HSTACK(u,r,COUNTIFS(Table1[result],I2:K2,Table1[module],u)))
Dynamic array formulas.

 

[sharshra]

As always, you are perfect @Fluff. This is what I was looking for I wonder how can you make it so fast

The summary is correct. Is it possible to add the header also thru the formula, based on the source table instead of hard coding?

excel problems.xlsx
	G	H	I	J	K
6	module	count of names	pass	fail	no result
7	C	11	6	5	1
8	L	13	7	4	2
Sheet1

Cell Formulas
Range	Formula
G7:K8	G7	=LET(u,UNIQUE(Table1[module]),r,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,ROWS(UNIQUE(FILTER(Table1[name],Table1[module]=y)))))),1),HSTACK(u,r,COUNTIFS(Table1[result],I2:K2,Table1[module],u)))
Dynamic array formulas.

 

[Fluff]

How about

=LET(u,UNIQUE(Table1[module]),r,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,ROWS(UNIQUE(FILTER(Table1[name],Table1[module]=y)))))),1),h,TOROW(UNIQUE(Table1[result])),VSTACK(HSTACK("Module","Count of names",h),HSTACK(u,r,COUNTIFS(Table1[result],h,Table1[module],u))))

 

[sharshra]

Perfect. Thanks a lot @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.