In Excel, concatenate the top 3 members in each group into a string
Below is a grouped table having detail data under each group:
A |
B |
C |
|
1 |
Client |
Location |
Sales |
2 |
ABC |
New York |
5,000 |
3 |
Florida |
4,000 |
|
4 |
Texas |
3,000 |
|
5 |
California |
2,000 |
|
6 |
Georgia |
1,000 |
|
7 |
XYZ |
Tennessee |
10,000 |
8 |
New Jersey |
8,000 |
|
9 |
Washington |
6,000 |
|
10 |
New York |
4,000 |
|
11 |
DEF |
Ohio |
7,500 |
12 |
Colorado |
5,000 |
|
13 |
HIJ |
Virginia |
8,000 |
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.
E |
F |
|
1 |
Client |
Top 3 Locaction |
2 |
ABC |
New York,Florida,Texas |
3 |
XYZ |
Tennessee,New Jersey,Washington |
4 |
DEF |
Ohio,Colorado |
5 |
HIJ |
Virginia |
Use SPL XLL to enter the formula below:
=spl("=?.group@i(~(1)).([~(1)(1),~.top(-3;~(3)).(~(2)).concat@c()])",A2:C13)
group@i groups rows by the specified condition; ~(1) represents the 1st member of the current row. top() function gets the top N members. concat@c concatenates members of a sequence with the comma.
Source:https://www.reddit.com/r/excel/comments/1d82y1w/is_it_possible_to_return_the_top_n_of_a_row_field/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
