Unique Values from a single cell

[pmurugan]

Hi,

I have a single cell comprising A|B|C| A. How do i get the unique values? Also please let me know if there is a chance to count the variables.

Thanks. I badly need a excel function to sort this out.

 

[John_w]

Try these functions, which can be called from VBA or as user-defined functions in a cell.

Public Function UniqueVar(cellValue As String) As String
    
    Static dict As Object
    Dim items As Variant, item As Variant
    Dim key As Variant
    
    items = Split(cellValue, "|")
    
    If dict Is Nothing Then Set dict = CreateObject("Scripting.Dictionary")

    For Each item In items
        dict(item) = item
    Next
    
    UniqueVar = ""
    For Each key In dict.Keys()
        UniqueVar = UniqueVar & key & " "
    Next
    
    dict.RemoveAll
    
    UniqueVar = Left(UniqueVar, Len(UniqueVar) - 1) + 1
    
End Function


Public Function CountVar(cellValue As String) As Integer
    
    Dim items As Variant
    
    items = Split(cellValue, "|")
    CountVar = UBound(items) - LBound(items)
    
End Function

I'm not sure whether the count you want is the count of all the values or just the unique values. CountVar() counts the former. If you want the latter then use similar code as UniqueVar(), and return dict.count.

 

[pmurugan]

Thanks a lot. John.