Look up unique values

The custom function below will return unique items from a range, you just supply the range you want to get the unique items from and the index number for the unique item you want to return. You can also use the function to return the count of uniqe values/items in a range.

Function UniqueItem(InputRange As Range, ItemNo As Long) As Variant
Dim cl As Range, cUnique As New Collection, cValue As Variant
    Application.Volatile
    On Error Resume Next
    For Each cl In InputRange
        If cl.Formula <> "" Then
            cUnique.Add cl.Value, CStr(cl.Value)
        End If
    Next cl
    UniqueItem = ""
    If ItemNo = 0 Then
        UniqueItem = cUnique.Count
    Else
        If ItemNo <= cUnique.Count Then
            UniqueItem = cUnique(ItemNo)
        End If
    End If
    On Error GoTo 0
End Function

Examples:
=UniqueItem(A1:A100,2) will return the 2nd unique value in the range A1:A100.
=UniqueItem(A1:A100,0) will return the count of unique values in the range A1:A100.

Document last updated 2000-02-05 12:44:52 Printerfriendly version