This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
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.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-08-23T07:36:26+00:00) #50 — Group And Summarize Rows And Add Different Words After Different Counts. Retrieved from https://www.scien.cx/2024/08/23/50-group-and-summarize-rows-and-add-different-words-after-different-counts/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.