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