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.
Task: Split detail data in each category, group them by category, get unique values of each group, and concatenate them using "comma+space".
Solution:
Use SPL XLL to enter the following formula:
=spl("=E@b(?.group(~1,~2;~.conj(~3.split@ct()).id().concat("","")))",A2:C12)
As shown in the picture below:
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)
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/
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...