Group Rows and Concatenate Cell Values

Judith-Excel-Sharing - Jul 10 - - Dev Community

Problem description & analysis:

Here is a categorized detail table:

original table

We need to group the table and concatenate the detail data using the semicolon.

desired table

Solution:

Use SPL XLL to do this:

=spl("=E@b(?.groups(~1;concat(~2;$[;])))",A2:B9)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

desired result table with code entered

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.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .