In Excel, Concatenate the Top 3 Members in Each Group into a String
esProc Desktop
Problem description & analysis:
Below is a grouped table having detailed 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 |
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.
SPL XLL is now FREE to download and apply. Come can try this solution on your own! It may inspire you a lot! http://www.scudata.com/download-Desktop
Subscribe to my newsletter
Read articles from esProc Desktop directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
esProc Desktop
esProc Desktop
esProc Desktop is a desktop data processing & analytics tool and is specifically designed for ordinary business people. It supports complex computations & spreadsheet data manipulation, can deal with tasks that are hard to accomplish in Excel, and in addition, generates more concise code than VBA and Python. esProc Desktop boasts all-around programming capabilities, supports multi-step interactive data analytics and is easy to use without configurations. This significantly lowers the technical threshold for data analytics.