finding 10 smallest unique values in a list

**reggie1000** · 10-02-2008, 10:19 AM

hello,

I have been using the SMALL function to find 10 smallest values in a list, however, some of the values are the same, is there a way of only having the 10 smallest unique values within a list?

Hopefully someone has an idea

thanks reg

**yawnzzzz** · 10-02-2008, 10:49 AM

Assuming your data starts in cell A1, then you can paste this in cell B1 and copy down.

Please Login or Register  to view this content.

It will change your list, so it only includes unique values.

Then, just change your small functions to point to this new range of data.

There are some better ways of handling this with the countif function, so you might consider playing around with it.

**Domenic** · 10-03-2008, 08:59 PM

Originally Posted by reggie1000

hello,

I have been using the SMALL function to find 10 smallest values in a list, however, some of the values are the same, is there a way of only having the 10 smallest unique values within a list?

Hopefully someone has an idea

thanks reg

Assuming that A2:A100 contains the data, try...

C2, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(FREQUENCY(A2:A100,A2:A100),1))

D2, confirmed with CONROL+SHIFT+ENTER, and copied down:

=IF(ROWS(D$2:D2)<=$C$2,SMALL(IF($A$2:$A$100<>"",IF(ISNA(MATCH($A$2:$A$100,$D$1:D1,0)),$A$2:$A$100)),1),"")

Hope this helps!