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.
Source link
lol
