Look up unique values

 2000-02-05    Lookup    0    48

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:

Return the 2nd unique value in the range A1:A100:
=UniqueItem(A1:A100,2)

Return the count of unique values in the range A1:A100:
=UniqueItem(A1:A100,0)


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.