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.

Q & A Collection

https://stackoverflow.com/questions/63726348/how-to-return-least-occuring-values-from-an-array-in-excel