ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address