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.

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.