Get summary results (Textjoin, Unique value) of a table

[mohsinbipu]

I want to get a summary of the unique value under a condition. Like in the below table-
1. For article BD-01, the Sorted (A-Z) unique area code is 11, 12, 15.
2. Then for these Unique codes, there are some product codes.
3. Now I want to Join all Area code and their Product codes with separator Collon( : ), Comma ( , ), Semi-colon ( ; )

Book1
	A	B	C	D	E	F
1	Input				Output
2	Article	Area Code	Product Code		Article	Area Code Product Code
3	BD-01	11	XY, Z-001		BD-01	11: XY, Z-001, JD, FL, MM, VN; 12: IN, ED; 15: BD
4	BD-02	10	IA		BD-02	10: IA, IA, ZZ, INL, X-001; 12: Japan; 15: VN
5	BD-03	13	HK		BD-03	11: BD; 13: HK, IN
6	BD-02	10	IA, ZZ
7	BD-02	12	Japan
8	BD-02	15	VN
9	BD-02	10	INL, X-001
10	BD-03	13	IN
11	BD-03	11	BD
12	BD-01	11	JD, FL, MM
13	BD-01	11	VN
14	BD-01	12	IN, ED
15	BD-01	15	BD
16
Sheet1

 

[Peter_SSs]

See if this does what you want.

23 11 16.xlsm
	A	B	C	D	E	F
1	Input				Output
2	Article	Area Code	Product Code		Article	Area Code Product Code
3	BD-01	11	XY, Z-001		BD-01	11: XY, Z-001, JD, FL, MM, VN; 12: IN, ED; 15: BD
4	BD-02	10	IA		BD-02	10: IA, IA, ZZ, INL, X-001; 12: Japan; 15: VN
5	BD-03	13	HK		BD-03	11: BD; 13: HK, IN
6	BD-02	10	IA, ZZ
7	BD-02	12	Japan
8	BD-02	15	VN
9	BD-02	10	INL, X-001
10	BD-03	13	IN
11	BD-03	11	BD
12	BD-01	11	JD, FL, MM
13	BD-01	11	VN
14	BD-01	12	IN, ED
15	BD-01	15	BD
mohsinbipu

Cell Formulas
Range	Formula
E3:F5	E3	=LET(Art,UNIQUE(A3:A15),HSTACK(Art,BYROW(Art,LAMBDA(a,LET(x,SORT(FILTER(B3:C15,A3:A15=a)),ac,UNIQUE(TAKE(x,,1)),TEXTJOIN("; ",,BYROW(ac,LAMBDA(rw,rw&": "&TEXTJOIN(", ",,FILTER(TAKE(x,,-1),TAKE(x,,1)=rw))))))))))
Dynamic array formulas.