Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to build a formula to walk a column of comma-separated values and count the number of unique values seen. I am using Excel 2010. I have a screenshot of some example data...

Screen shot of data

The output of the formula in this case should be 5. In other words, the formula must count the following values: 2.3.0, 2.4.1, 2.4.2, 2.4.3, 2.4.4

I have figured out how I can simply count the raw number of comma-separated values like this...

=SUMPRODUCT(--(M123:M127<>""),LEN(M123:M127)-LEN(SUBSTITUTE(M123:M127,",",""))+1)

However, that formula yields 7, because it counts 2.4.3 and 2.4.4 twice.

I'm at a loss for how to reject repeated comma-separated values in different cells of the same column.

Due to the other systems that must interface with the spreadsheet, answers can only use a formula; they must not use VBA or some kind of filter.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

If you can add some intermediate formulas to your sheet, here's a possible solution.

Based on the ranges in your example:

Put in cell N123 This returns the part to the left of , or the whole cell if no ,

=IFERROR(LEFT($M123,FIND(",",$M123)-1),M123)

Put in cell O123 This returns the part to the right of , or - if no ,

=IFERROR(TRIM(MID($M123,FIND(",",$M123)+1,999)),"-")

Put in cell P123 This returns 1 if cell N123 is unique in the list, plus 1 if cell O123 is unique

=AND(ROW()+1=ROW($N$123:$N$127)+MATCH($N123,$N$123:$N$127,0),$N123<>"-")
+AND(ROW()=IFERROR(MATCH($O123,$N$123:$N$127,0),""),$O123<>"-")
+AND(ISNA(MATCH($N123,$N$123:$N$127,0)),ROW()+1=IFERROR(ROW($N$123:$N$127) 
    +MATCH($N123,$O$123:$O$127,0),FALSE),$N123<>"-")
+AND(ISNA(MATCH($O123,$N$123:$N$127,0)),ROW()+1=ROW($N$123:$N$127)
    +MATCH($O123,$O$123:$O$127,0),O123<>"-")

Copy cells N123..P123 down to N123..P127

The number of unique items is =SUM(P123:Q127)

This assumes there are at most 2 items in each data cell. If this is not the case you will need to add more intermediate columns, and extend the formula in O123 accordingly. If there are many more than 2, this will quickly get out of hand.

FWIW, you say a VBA solution is not posible, but is a VBA user defined function ok?

It would look something like =CountUnique(M123:M127)

share|improve this answer
add comment

Based on Chris Neilsen's input, I am going to try to modify our systems and use this... Source: Mr Excel Forums - Post dated 1 Jan, 2010 at 10:04am

Function UNIQUECOUNTIF(ByRef SR As Range, _
                        ByRef RR As Range, _
                        Optional ByVal Crit As Variant, _
                        Optional NCOUNT As Boolean = False, _
                        Optional POSTCODE As Boolean = False) As Long
Dim K1, K2, i As Long, c As Long, x, n As Long
K1 = SR: K2 = RR
With CreateObject("scripting.dictionary")
    For i = 1 To UBound(K1, 1)
        If Not IsMissing(Crit) Then
            If LCase$(K1(i, 1)) = LCase$(Crit) Then
                If POSTCODE Then
                    x = Split(Replace(LCase$(K2(i, 1)), ",", " "), " ")
                Else
                    x = Split(LCase$(K2(i, 1)), ",")
                End If
                For c = 0 To UBound(x)
                    If POSTCODE Then
                        If IsNumeric(x(c)) Then
                            If Not .exists(x(c)) Then
                                .Add x(c), 1
                            ElseIf NCOUNT Then
                                .Item(x(c)) = .Item(x(c)) + 1
                            End If
                        End If
                    Else
                        If Not .exists(x(c)) Then
                            .Add x(c), 1
                        ElseIf NCOUNT Then
                            .Item(x(c)) = .Item(x(c)) + 1
                        End If
                    End If
                Next
            End If
        Else
            If POSTCODE Then
                x = Split(Replace(LCase$(K2(i, 1)), ",", " "), " ")
            Else
                x = Split(LCase$(K2(i, 1)), ",")
            End If
            For c = 0 To UBound(x)
                If POSTCODE Then
                    If IsNumeric(x(c)) Then
                        If Not .exists(x(c)) Then
                            .Add x(c), 1
                        ElseIf NCOUNT Then
                            .Item(x(c)) = .Item(x(c)) + 1
                        End If
                    End If
                Else
                    If Not .exists(x(c)) Then
                        .Add x(c), 1
                    ElseIf NCOUNT Then
                        .Item(x(c)) = .Item(x(c)) + 1
                    End If
                End If
            Next
        End If
    Next
    If .Count > 0 Then UNIQUECOUNTIF = Application.Sum(.items)
End With
End Function
share|improve this answer
add comment

Here's another solution using VBA functions. Paste the following into a module.

Function ListCount(list As String, delimiter As String) As Long
Dim arr As Variant
arr = Split(list, delimiter)
ListCount = UBound(arr) - LBound(arr) + 1
End Function

Private Function RemoveDuplicates(list As String, delimiter As String) As String
Dim arrSplit As Variant, i As Long, tmpDict As New Dictionary, tmpOutput As String
arrSplit = Split(list, delimiter)
For i = LBound(arrSplit) To UBound(arrSplit)
    If Not tmpDict.Exists(arrSplit(i)) Then
        tmpDict.Add arrSplit(i), arrSplit(i)
        tmpOutput = tmpOutput & arrSplit(i) & delimiter
    End If
Next i
If tmpOutput <> "" Then tmpOutput = Left(tmpOutput, Len(tmpOutput) - Len(delimiter))
RemoveDuplicates = tmpOutput
'housekeeping
Set tmpDict = New Dictionary
End Function

Then in your worksheet, you can use the following formula:

=ListCount(RemoveDuplicates(A1,", "),", ")

where A1 is your comma-separated list.

NOTE: You will have to add the Microsoft Scripting Runtime reference to your VBA project (under Tools > References... in the VBA Project Editor window).

share|improve this answer
    
If you don't mind, you could convert early binding to late binding. This way he and all other users don't have to add the reference. Early binding usually leads to a lot of confusion. In my own firm too: "Hey, your script is not working. Come here and fix it asap!" –  nixda Aug 20 '13 at 17:14
    
@nixda True. I just pulled the RemoveDuplicates function from another project of mine, so I didn't write the code with other users in mind. Feel free to make the change yourself if you want. –  Excellll Aug 20 '13 at 17:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.