DEV Community

Cover image for Split & Group Text and Perform Distinct on Each Group
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

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.

original table

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

desired table

Solution:

Use SPL XLL to enter the following formula:

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

As shown in the picture below:

result table with code entered

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.

Top comments (2)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Please feel free to leave your Excel difficulties in our Discord or Reddit communities! We have experts who are kind and generous enough to offer a hand: 👨‍💻 discord.gg/hgbKEvJ4 & 🙌🏻 reddit.com/r/esProc_Desktop/

Collapse
 
judith677 profile image
Judith-Excel-Sharing

For more detailed information related to SPL XLL:

SPL download address: scudata.com/download-Desktop

Plugin Installation Method: c.scudata.com/article/1652061135502

References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524

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