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