Fine tuning of single cell formula

[sharshra]

I´m using single cell formula to get the summary table. I´m making some mistake & not getting the desired output. Can the experts in this forum help please?

Problems (compare the desired output table & output from single cell formula) :
1. I want to get the ids for each person split between modules. Now I´m getting the combined ids for each person in the output table.
2. I want to get the count of ids for each output row. I tried few options but failed.
3. Module & person distribution is not happening correctly. It should be as shown in the desired output table.

Output table (Single cell formula):

excel problems.xlsx
	M	N	O	P
2	module	person	count of id	id
3	co	a	1, 2, 3, 4, 5, 8
4	cl	b	3, 6, 9, 10
5	cl	c	3, 6
6	cl	d	4, 7
Sheet3

Cell Formulas
Range	Formula
M3:O6	M3	=LET( id,B3:B17, p,D3:D17, m,C3:C17, uP,UNIQUE(FILTER(p,p<>"")), mName,XLOOKUP(uP,p,m), pId, MAP(uP,LAMBDA(x,TEXTJOIN(", ",,UNIQUE(FILTER(id,p=x))))), HSTACK(mName,uP,pId))
Dynamic array formulas.

Source table:

excel problems.xlsx
	B	C	D
2	id	module	person
3	1	co	a
4	2	co	a
5	3	cl	b
6	3	cl	c
7	3	cl	a
8	4	cl	a
9	4	cl	d
10	5	co	a
11	6	co	b
12	6	co	c
13	6	co	c
14	7	cl	d
15	8	cl	a
16	9	cl	b
17	10	co	b
Sheet3

Desired output:

excel problems.xlsx
	F	G	H	I	J
2	module	person	count of id	id	Remarks
3	cl	a	3	3, 4, 8
4	co	a	3	1, 2, 5
5	cl	b	2	3, 9
6	co	b	2	6, 10
7	cl	c	1	3
8	co	c	1	6	duplicate removed
9	cl	c	2	4, 7
Sheet3

 

[hagia_sofia]

Hello, would this address it?

=LET(
id,A2:A16,
module,B2:B16,
person,C2:C16,
mp,B2:C16,
a,SORT(UNIQUE(mp),{2,1}),
b,MAP(CHOOSECOLS(a,1),CHOOSECOLS(a,2),LAMBDA(a,b,ROWS(UNIQUE(FILTER(id,(module&person=a&b)))))),
c,DROP(REDUCE("",CHOOSECOLS(a,1)&CHOOSECOLS(a,2),LAMBDA(a,b,VSTACK(a,TEXTJOIN(", ",,SORT(UNIQUE(FILTER(id,(module&person=b)))))))),1),
HSTACK(a,b,c))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1
2		id	module	person		module	person	count of id	id
3		1	co	a		cl	a	3	3, 4, 8
4		2	co	a		co	a	3	1, 2, 5
5		3	cl	b		cl	b	2	3, 9
6		3	cl	c		co	b	2	6, 10
7		3	cl	a		cl	c	1	3
8		4	cl	a		co	c	2	6
9		4	cl	d		cl	d	2	4, 7
10		5	co	a
11		6	co	b
12		6	co	c
13		6	co	c
14		7	cl	d
15		8	cl	a
16		9	cl	b
17		10	co	b
Data

Cell Formulas
Range	Formula
F3:I9	F3	=LET(f,FILTER(B3:D100,B3:B100<>""),u,SORT(UNIQUE(DROP(f,,1)),{2,1}),HSTACK(u,COUNTIFS(C:C,INDEX(u,,1),D:D,INDEX(u,,2)),BYROW(u,LAMBDA(br,TEXTJOIN(", ",,UNIQUE(FILTER(B3:B100,(C3:C100=INDEX(br,,1))*(D3:D100=INDEX(br,,2)))))))))
Dynamic array formulas.

 

[felixstraube]

One more option:

Book9.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		id	module	person
3		1	co	a		cl	a	3	3, 4, 8
4		2	co	a		co	a	3	1, 2, 5
5		3	cl	b		cl	b	2	3, 9
6		3	cl	c		co	b	2	6, 10
7		3	cl	a		cl	c	1	3
8		4	cl	a		co	c	1	6	Duplicates removed
9		4	cl	d		cl	d	2	4, 7
10		5	co	a
11		6	co	b
12		6	co	c
13		6	co	c
14		7	cl	d
15		8	cl	a
16		9	cl	b
17		10	co	b
Sheet4

Cell Formulas
Range	Formula
F3:J9	F3	=LET(d,B3:D17, id,CHOOSECOLS(d,1), module,CHOOSECOLS(d,2), person,CHOOSECOLS(d,3), uModulePerson,UNIQUE(CHOOSECOLS(d,2,3)), ids,BYROW(uModulePerson,LAMBDA(x, TEXTJOIN(", ",,FILTER(id,(module=CHOOSECOLS(x,1))*(person=CHOOSECOLS(x,2)))))), uids,BYROW(uModulePerson,LAMBDA(x, TEXTJOIN(", ",,UNIQUE(FILTER(id,(module=CHOOSECOLS(x,1))*(person=CHOOSECOLS(x,2))))))), nids,BYROW(uModulePerson,LAMBDA(x, COUNTA(UNIQUE(FILTER(id,(module=CHOOSECOLS(x,1))*(person=CHOOSECOLS(x,2))))))), SORT(SORT(HSTACK(uModulePerson,nids,uids,IF(uids=ids,"","Duplicates removed")), 1),2) )
Dynamic array formulas.

 

[sharshra]

Thank you very much, @Fluff , @felixstraube , @hagia_sofia . I have used the formula from Fluff & marking it as the solution since only one can be marked as solution. However, the formula provided by Felix & hagia_sofia are also useful. Thanks for that. It is really a great forum because of fabulous people like you

 

[Fluff]

Glad we could help & thanks for the feedback.