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

Problem description & analysis:

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

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.

Solution:

Use SPL XLL to enter the formula below:

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

As shown in the picture below:

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.