# How can I count unique comma-separated values in Excel 2010

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...

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.

-

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)`

-

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

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
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).

-
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

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
ElseIf NCOUNT Then
.Item(x(c)) = .Item(x(c)) + 1
End If
End If
Else
If Not .exists(x(c)) Then
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
ElseIf NCOUNT Then
.Item(x(c)) = .Item(x(c)) + 1
End If
End If
Else
If Not .exists(x(c)) Then