In Excel, Concatenate the Top 3 Members in Each Group into a String

Judith-Excel-Sharing - Jun 13 - - Dev Community

Problem description & analysis:

Below is a grouped table having detailed data under each group:

the grouped table

We need to concatenate the top 3 locations in each group into a string with the comma and display them along with the group header.

the desired result table

Solution:

Use SPL XLL to enter the formula below:

=spl("=?.group@i(~(1)).([~(1)(1),~.top(-3;~(3)).(~(2)).concat@c()])",A2:C13)

Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

the result table with code entered
Explanation:

group@i groups rows by the specified condition; ~(1) represents the 1st member of the current row. the top() function gets the top N members. concat@c concatenates members of a sequence with the comma.

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