List Values in a Specific Column Whose Occurrences Rank in Top 10
Problem description & analysis
We have an Excel file Book1.xlsx. Below is part of the data in column A:
| A |
|
| 1 |
DQ-PFKH-535687-7772 |
| 2 |
TQ-ZXLQ-591095-5009 |
| 3 |
SD-KXRS-638318-5117 |
| 4 |
DV-QPRV-089800-1971 |
| 5 |
DQ-PFKH-535687-7772 |
| 6 |
XX-LDRD-393866-8446 |
| 7 |
HF-WBZX-343158-2746 |
| 8 |
SD-KXRS-638318-5117 |
| 9 |
KG-WJAE-345697-4353 |
| 10 |
… |
We are trying to list values whose occurrences rank in top 10, as shown below:
| B |
|
| 1 |
TQ-ZXLQ-591095-5009 |
| 2 |
XX-LDRD-393866-8446 |
| 3 |
IX-SSIH-101676-3379 |
| 4 |
DV-QPRV-089800-1971 |
| 5 |
FB-MJRQ-394745-7234 |
| 6 |
SD-KXRS-638318-5117 |
| 7 |
WM-PRKS-349491-3217 |
| 8 |
HF-WBZX-343158-2746 |
| 9 |
KG-WJAE-345697-4353 |
| 10 |
ND-RNMQ-345711-6881 |
Solution & explanation
First, we load in add-in ExcelRaq.xll to Excel, then
Select cells from B1 to B10 and enter the following formula in them: =esprocT("=?.group(~;~.len()).top(-10;#2).(#1)",A1:A100) and press Ctrl+Shift+Enter to get results.
https://stackoverflow.com/questions/63620983/excel-formula-top-10-most-repeated-values-in-a-list
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
