Split & Group Text and Perform Distinct on Each Group

Problem description & analysis:

Below is an irregularly categorized detail table. Column A and column B are categories and both have duplicate values. Column C contains detailed data consisting of strings separated by "comma+space", and there are duplicates among the string values.

	A	B	C
1	Project #	Project Step	Participant(s)
2	100	101	John J
3	100	102	Dave M, Phil X
4	100	102	Dave M, Lisa P, John J
5	100	103	Phil X, Lisa P
6	100	104	Dave M
7	200	201	John J, Lisa P, Alice T
8	200	201	Lisa P, Alice T
9	200	202	Dave M, Lisa P, John J
10	200	203	Phil X, Lisa P
11	200	204	Dave M, Phil X
12	200	204	Dave M, Lisa P, John J

Task: Split detail data in each category, group them by category, get unique values of each group, and concatenate them using "comma+space".

	E	F	G
1	Project #	Project Step	List Participant(s)
2	100	101	John J
3	100	102	Dave M, John J, Lisa P, Phil X
4	100	103	Lisa P, Phil X
5	100	104	Dave M
6	200	201	Alice T, John J, Lisa P
7	200	202	Dave M, John J, Lisa P
8	200	203	Lisa P, Phil X
9	200	204	Dave M, John J, Lisa P, Phil X

Solution:

Use SPL XLL to enter the following formula:

=spl("=E@b(?.group(~1,~2;~.conj(~3.split@ct()).id().concat("","")))",A2:C12)

Explanation:

group()function groups rows and handles data in each group; ~1 represents the first sub-member of the current member; split@ct splits each string by comma and performs trim operation to remove spaces at both sides; id() removes duplicate members. E@b converts the Excel table to a sequence without titles.

For more detailed information related to SPL XLL:

SPL download address: esProc Desktop Download

Plugin Installation Method: SPL XLL Installation and Configuration

References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

SPL Programming (YouTube FREE courses): https://www.youtube.com/playlist?list=PLQeR-IhHo7qNCw6o7PW8YfHvRx8pgzZso