Excel Find the Least Occurring Values
Problem description
The following Excel file has a column of month values. A month appears in an indefinite number of times, as shown below:
We are counting the frequencies of each month’s appearances, and trying to find the month/months with the smallest count value. List the month or months in column C, as shown below:
Solution
Use SPL XLL plug-in
Write the formula in the blank cell:
=spl("=?.group(trim(~(1))).minp@a(~.count()).(~(1))",A2:A20)
As shown:
Return:
Explanation:
Rearrange the data by removing extra white spaces from certain data, and then divide values into groups.
Count the occurrences of each month and get all months having the smallest count.
Get value of the first item from each group.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/