Using the small function, is there a way with one formula to get a
list of unique small values.
If I had the following:
1
1
2
4
4
6
9
11
11
13
If I look up =small(a1:10,1) it gives me 1
If I look up =small(a1:10,2) it gives me 1
What I am looking for is a formula that will give me
1
2
4
6
9
11
13
Any help would be appreciated! Thanks in advance!
You can use a User Defined Function to return an array of unique values, and
then use this formula in some cell.
=IF(COUNT(uniques(rng))<ROWS($1:1),"",SMALL(uniques(rng),ROWS($1:1)))
If you fill down, the formula will adjust to return each unique value, sorted
from lowest to highest, and return blanks when you've "gone too far".
To enter the User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
If you need to omit blanks or zero's, that can be added to the UDF code.
=========================================
Option Explicit
Function uniques(rg As Range) As Variant
Dim c As Range
Dim u As Collection, o As Object
Dim t() As Variant, i As Long
Set u = New Collection
On Error Resume Next
For Each c In rg
u.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
ReDim t(1 To u.Count)
For i = 1 To u.Count
t(i) = u(i)
Next i
uniques = t
End Function
=============================
--ron
