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
Subscribe to my newsletter
Read articles from esProc Desktop directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by

esProc Desktop
esProc Desktop
esProc Desktop is a desktop data processing & analytics tool and is specifically designed for ordinary business people. It supports complex computations & spreadsheet data manipulation, can deal with tasks that are hard to accomplish in Excel, and in addition, generates more concise code than VBA and Python. esProc Desktop boasts all-around programming capabilities, supports multi-step interactive data analytics and is easy to use without configurations. This significantly lowers the technical threshold for data analytics.