Filter Function need to *** TextJoin to it

[Stephen_IV]

I have a filter function that I would like to add Textjoin to make 1 formula. Can someone please help me with this. Thanks in advance!

SPED BIL REG Class Breakdown FIlter.xlsm A B C D E F G H I J K L M N 1 SCHOOLID GRADE_LEVEL SCHOOL FIRST_NAME LAST_NAME TYPE FIRST_NAME LAST_NAME TYPE GRADE 2 150211 5 Washington Susan DDD REGULAR_ED SPED Humera FFF SPED-SUBSTITUTE 0,1,2 3 150211 3 Washington Kathleen EEE REGULAR_ED 150211 Tiffany PPP SPED-INTEGRATED-ASD 1,2 4 150211 0 Washington Humera FFF SPED-SUBSTITUTE 5 150211 0 Washington Humera FFF SPED-SUBSTITUTE 6 150211 1 Washington Humera FFF SPED-SUBSTITUTE 7 150211 2 Washington Humera FFF SPED-SUBSTITUTE 8 150211 2 Washington Kathleen FFG REGULAR_ED 9 150211 1 Washington Laura GGG REGULAR_ED 10 150211 1 Washington Susan GGO REGULAR_ED 11 150211 6 Washington Joshua LLL REGULAR_ED 12 150211 3 Washington Jacqueline MMM REGULAR_ED 13 150211 -1 Washington Rosanne MMM REGULAR_ED 14 150211 0 Washington Shannon NNN REGULAR_ED 15 150211 5 Washington Dawn OOO REGULAR_ED 16 150211 4 Washington Rocco PAC REGULAR_ED 17 150211 1 Washington Tiffany PPP SPED-INTEGRATED-ASD 18 150211 1 Washington Tiffany PPP SPED-INTEGRATED-ASD 19 150211 2 Washington Tiffany PPP SPED-INTEGRATED-ASD 20 150211 4 Washington Hillary SSS REGULAR_ED 21 150211 6 Washington Gabriele TTT REGULAR_ED 22 150211 1 Washington Dina VVV REGULAR_ED 23 150211 -1 Washington Heather YYY REGULAR_ED Sheet3 (2) Cell Formulas Range Formula K2:M3 K2 =UNIQUE(FILTER(D2:F23,--ISNUMBER(SEARCH(J2,$F$2:$F$23))*($A$2:$A$23=J3))) N2:N3 N2 =TEXTJOIN(",",1,UNIQUE(IF((D2:D23=K2)*(E2:E23=L2),B2:B23,""))) Dynamic array formulas.

 

[Stephen_IV]

Closest I can get. Seems to do what I want.

=VSTACK({"Name","Type","Grade"},LET(
    f,FILTER(D2:D870 & " " & E2:E870, (--ISNUMBER(SEARCH(M2,$F$2:$F$870))*($A$2:$A$870=M3))*(A2:A870 = M3)),
    u,UNIQUE(f),
    h,BYROW(u,LAMBDA(x,TEXTJOIN(", ",TRUE,UNIQUE(FILTER(F2:F870,(D2:D870 & " " & E2:E870)=x))))),
    g,BYROW(u,LAMBDA(x,TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B2:B870,(D2:D870 & " " & E2:E870)=x))))),
    SORT(HSTACK(u,h,g))
))

 

[Peter_SSs]

That isn't a valid formula for me. In any case, what about this one?

24 01 14.xlsm
	A	B		D	E	F			I	J	K	L	M	N
1	SCHOOLID	GRADE_LEVEL		FIRST_NAME	LAST_NAME	TYPE					FIRST_NAME	LAST_NAME	TYPE	GRADE
2	150211	5		Susan	DDD	REGULAR_ED				SPED	Humera	FFF	SPED-SUBSTITUTE	0,1,2
3	150211	3		Kathleen	EEE	REGULAR_ED				150211	Tiffany	PPP	SPED-INTEGRATED-ASD	1,2
4	150211	0		Humera	FFF	SPED-SUBSTITUTE
5	150211	0		Humera	FFF	SPED-SUBSTITUTE
6	150211	1		Humera	FFF	SPED-SUBSTITUTE
7	150211	2		Humera	FFF	SPED-SUBSTITUTE
8	150211	2		Kathleen	FFG	REGULAR_ED
9	150211	1		Laura	GGG	REGULAR_ED
10	150211	1		Susan	GGO	REGULAR_ED
11	150211	6		Joshua	LLL	REGULAR_ED
12	150211	3		Jacqueline	MMM	REGULAR_ED
13	150211	-1		Rosanne	MMM	REGULAR_ED
14	150211	0		Shannon	NNN	REGULAR_ED
15	150211	5		Dawn	OOO	REGULAR_ED
16	150211	4		Rocco	PAC	REGULAR_ED
17	150211	1		Tiffany	PPP	SPED-INTEGRATED-ASD
18	150211	1		Tiffany	PPP	SPED-INTEGRATED-ASD
19	150211	2		Tiffany	PPP	SPED-INTEGRATED-ASD
20	150211	4		Hillary	SSS	REGULAR_ED
21	150211	6		Gabriele	TTT	REGULAR_ED
22	150211	1		Dina	VVV	REGULAR_ED
23	150211	-1		Heather	YYY	REGULAR_ED
Sheet2 (3)

Cell Formulas
Range	Formula
K2:N3	K2	=LET(u,UNIQUE(FILTER(D2:F23,ISNUMBER(SEARCH(J2,F2:F23))*(A2:A23=J3))),HSTACK(u,BYROW(u,LAMBDA(r,TEXTJOIN(",",1,UNIQUE(IF((D2:D23=INDEX(r,1,1))*(E2:E23=INDEX(r,1,2)),B2:B23,"")))))))
Dynamic array formulas.

 

[Stephen_IV]

Thank you Peter_SSs. That formula is perfect. Thank you again!

 

[Peter_SSs]

You are welcome. Thanks for the confirmation.