Problem description & analysis:
Here is a categorized detail table:
A | B | |
1 | Name | Name 2 |
2 | Unique 1 | ex 1 |
3 | Unique 1 | ex 2 |
4 | Unique 1 | ex 3 |
5 | Unique 2 | ext 1 |
6 | Unique 2 | ext 2 |
7 | Unique 2 | ext 3 |
8 | Unique 2 | ext 4 |
9 | Unique 2 | ext 5 |
We need to group the table and concatenate the detail data using the semicolon.
D | E | |
1 | Unique 1 | ex 1;ex 2;ex 3 |
2 | Unique 2 | ext 1;ext 2;ext 3;ext 4;ext 5 |
Solution:
Use SPL XLL to do this:
=spl("=E@b(?.groups(~1;concat(~2;$[;])))",A2:B9)
As shown in the picture below:
Explanation:
E@b function converts the two-dimensional table to a sequence. ~1 represents the first sub-member of the current member; and $[] represents a string.
Please free to download SPL XLL and explore the data processing journey on your own⬇️
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