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

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.

Comments

[Judith-Excel-Sharing]

How do you find this solution provided by SPL XLL?🧐 Please feel free to leave any thoughts in the comment section! We would love to discuss more details with YOU!
Also, if you find SPL XLL solutions interesting, you can try them on your own at NO cost right now👉🏻scudata.com/download-Desktop

[Eckehard]

It's just boring. Excel offers more than enough out of the box, and it is pretty simple to build own functions to extend, so, most of the examples you provide can be done easier without SPL XLL.

[Judith-Excel-Sharing]

Then you must be an Excel master👏Maybe our examples didn’t highlight SPL XLL’s unique benefits as well as they could have. If you have any particularly challenging Excel problems, please share them with us. We’d love to see how SPL XLL can tackle them!

[Judith-Excel-Sharing]

We also offer Excel help in our Discord and Reddit communities🙌🏻. If you have any complex Excel puzzles, the Excel experts there are generous enough to give you a hand! discord.gg/hgbKEvJ4 & reddit.com/r/esProc_Desktop/