#50 - Group And Summarize Rows And Add Different Words After Different Counts
Problem description & analysis:
An Excel table has two columns:
| A | B | |
| 1 | Apples | 3 |
| 2 | Apples | 0 |
| 3 | Bananas | 1 |
| 4 | Bananas | 6 |
| 5 | Cantaloupe | 0 |
| 6 | Kiwis | 2 |
| 7 | Kiwis | 2 |
| 8 | Kiwis | 1 |
Task: Group the table by the 1st column, and add 1 if the 2nd column in the current group is greater than 0; otherwise, do not add 1. If the count equals 1, add the word “Occurrence” after it; if it doesn’t, add its plural form “Occurrences” after it. Below is the expected result:
| A | B | |
| 1 | Apples | 1 Occurrence |
| 2 | Bananas | 2 Occurrences |
| 3 | Cantaloupe | 0 Occurrences |
| 4 | Kiwis | 3 Occurrences |
Solution:
Use SPL XLL to do this:
=spl("=E@b(?.group(~1;t=~.count(~2>0) / if(t==1,""Occurrence"",""Occurrences"")))",A1:B8)
As shown in the picture below:
Explanation:
group() function groups rows and handles each group of data. E@b removes column titles. ~1 represents the 1st child member of the current member in a sequence.
Feel free to download esProc Desktop and explore the data processing journey yourself ⬇️
SPL download address: esProc Desktop Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
SPL Programming (YouTube FREE courses): https://www.youtube.com/playlist?list=PLQeR-IhHo7qNCw6o7PW8YfHvRx8pgzZso