#50 — Group And Summarize Rows And Add Different Words After Different Counts

Problem description & analysis:

An Excel table has two columns:

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:

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.