Count number of unique values in a single cell separated by commas

[crackod93]

Hi, probably a very easy one here, im looking to count the unique number of values within a single cell string which are separated by commas

example:

Cell A1 contains: 1,2,3A33,444,2,1,16

The resulting formula would give a count of 5 unique value within cell A1 as values '2' and '1' are repeated twice within the cell and would therefore be excluded.

Any help would be most appreciated, thank you!

 

[russellrichter]

Probably the cleanest way to do this would be to create a function in VBA like the one below. Then, your formula would be =UniqueValues(A1). If you were to try to do a one-cell formula that parses the string in A1 into individual tokens and then counts up the unique values, I think you'd have one big hairy formula, assuming it's possible. (And, if it is possible, the members on this forum will do it!)

RGR

Public Function UniqueValues(InputString As String)
Dim Token() As String
Dim TokenCount, TokenStart, TokenEnd As Integer
Dim UniqueToken As Boolean
    TokenStart = 1
    For c = 1 To Len(InputString)
        If Mid(InputString, c, 1) = "," Then
            TokenCount = TokenCount + 1
            ReDim Preserve Token(TokenCount)
            Token(TokenCount) = Mid(InputString, TokenStart, c - TokenStart)
            UniqueToken = True
            For t = 1 To TokenCount - 1
                If Token(t) = Token(TokenCount) Then
                    UniqueToken = False
                End If
            Next
            If UniqueToken = True Then UniqueValues = UniqueValues + 1
            TokenStart = c + 1
        End If
    Next
    TokenCount = TokenCount + 1
    ReDim Preserve Token(TokenCount)
    Token(TokenCount) = Mid(InputString, TokenStart, c - TokenStart)
    UniqueToken = True
    For t = 1 To TokenCount - 1
        If Token(t) = Token(TokenCount) Then
            UniqueToken = False
        End If
    Next
    If UniqueToken = True Then UniqueValues = UniqueValues + 1
    TokenStart = c + 1
End Function

 

[pgc01]

Hi

This is a formula solution.

I chose to use a name to make the formula smaller

- with B1 selected, define the name

Name: _Arr
Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!A1,",",""",""")&"""}")

- In B1 enter the formula:

=SUM((2+LEN(_Arr))/(2+LEN(SUBSTITUTE(A1,",",",,"))-LEN(SUBSTITUTE(","&SUBSTITUTE(A1,",",",,")&",",","&_Arr&",",""))))

This is an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

 

[swinglow]

what is EVALUATE?

never mind

Evaluate:http://www.vertex42.com/ExcelArticles/evaluate-function.html