DEV Community

Cover image for In Excel, Concatenate the Top 3 Members in Each Group into a String
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

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:

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.

Top comments (4)

Collapse
 
judith677 profile image
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

Collapse
 
efpage profile image
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.

Collapse
 
judith677 profile image
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!

Collapse
 
judith677 profile image
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/