Table manipulations using dynamic arrays

[sharshra]

I have to derive an output as shown in the 'desired output' table based on a 'source table' using the logic given below. I got partially correct result, but there are few issues. Can the experts help please?

Source table has the id, ph, name & dept. The output table should have the ids for the combination of name & dept in ph 1 & 2. I see that the result is incorrect when same name is in multiple dept. It just counts the name in all ph & duplicate the result for dept.

For example, trtr should have 3 rows in the output with different ids. But all ids of trtr are duplicated against depts c, l & a. Cells with incorrect output are marked in pink.

Source table:

excel problems.xlsx
	B	C	D	E
21	id	ph	name	dept
22	8	1	kjkj	c
23	71	1	yuyu	l
24	39	2	kjkj	a
25	54	1	trtr	c
26	23	1	nb	c
27	6	1	nb	c
28	91	1	trtr	l
29	2	1	trtr	a
30	7	2	nb	c
31	64	2	kjkj	a
Sheet8

Desired output:

excel problems.xlsx
	H	I	J	K	L	M
21	dept	name	ph 1 count	ph1	ph2 count	ph2
22	l	yuyu	1	71
23	l	trtr	1	91
24	c	kjkj	1	8
25	c	trtr	2	54
26	c	nb	2	23, 6	1	7
27	a	kjkj			2	39, 64
28	a	trtr	1	2
Sheet8

Actual output:

excel problems.xlsx
	P	Q	R	S	T	U
21	dept	name	ph 1 count	ph1	ph2 count	ph2
22	l	yuyu	1	71
23	l	trtr	3	54, 91, 2
24	c	kjkj	1	8	2	39, 64
25	c	trtr	3	54, 91, 2
26	c	nb	2	23, 6	1	7
27	a	kjkj	1	8	2	39, 64
28	a	trtr	3	54, 91, 2
Sheet8

Cell Formulas
Range	Formula
P22:U28	P22	=LET( all, $B$22:$E$31, name, CHOOSECOLS(all, 3), ph, CHOOSECOLS(all, 2), dptName, SORT(UNIQUE(CHOOSECOLS(all, 4,3)),,-1), idDptName, LAMBDA(phs, IFERROR(CHOOSECOLS(MAP(dptName, LAMBDA(x, TEXTJOIN(", ", ,UNIQUE(CHOOSECOLS(FILTER(all, (name=x)*(ph=phs)),1))))),2),"")), idDptName1, idDptName(1), idDptName2, idDptName(2), idDptNameCount, LAMBDA(phs, IFERROR(CHOOSECOLS(MAP(dptName, LAMBDA(x, TEXTJOIN(", ", ,ROWS(UNIQUE(CHOOSECOLS(FILTER(all, (name=x)*(ph=phs)),1)))))),2),"")), idDptNameCount1, idDptNameCount(1), idDptNameCount2, idDptNameCount(2), HSTACK(dptName,idDptNameCount1,idDptName1, idDptNameCount2,idDptName2))
Dynamic array formulas.

 

[Fluff]

Glad we could help & thanks for the feedback.