# unique count of values in a cell separated by comma in excel

How do I get the unique count of values in a cell separated by comma in excel?

For example: cell B13 has the value

`1,1,2,3,7,1`

Using the below formula we get the count of values separated by `,` as 6.

`=1+LEN(B13)-LEN(SUBSTITUTE(B13,";",""))`

But I want to count the unique values in the cell B13, which is 4. Can someone help me achieve this?

Note: This question is similar to How can I count unique comma-separated values in Excel 2010. However, this is a special, restrictive case (the values are only single digits), which allows solutions that would not apply to the other question.

• Does the list contain only single digits or can there be numbers greater than 9? – fixer1234 Jan 6 '15 at 6:33
• @MikeFitzpatrick - The general requirement is similar to that question. However, this is a restrictive case (only single digits), that allows solutions that would not apply in that question. – fixer1234 Jan 24 '15 at 6:24
• @fixer1234, yes, it's a restricted case now that you edited the question. – Mike Fitzpatrick Jan 24 '15 at 8:33

Install the following User Defined Function (UDF) in a standard module:

``````Public Function CountUnique(r As Range) As Long
Dim c As Collection
Set c = New Collection
ary = Split(r.Text, ",")
On Error Resume Next
For Each a In ary
If Err.Number = 0 Then
CountUnique = CountUnique + 1
Else
Err.Number = 0
End If
Next a
On Error GoTo 0
End Function
``````

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

``````=CountUnique(A1)
``````

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

For example:

• Hi Gary's student, Your UDF worked but its not giving the correct count. This is our value in the cell (GEN-001,GEN-001,GEN-001,GLN-003,GLN-003) .The UDF function is returning the value 3 instead of 2.Can you please help us? – thunder Jan 12 '15 at 11:10
• @thunder Did you type those into your comment, or copy/paste from the cell? I get a value of two (2) using GS's code. If you did not copy/paste, then probably there is a space after one of the commas. You could account for this by doing a Trim before adding the value to the collection – Ron Rosenfeld Jan 24 '15 at 12:14
• @RonRosenfeld I should have done that myself.... – Gary's Student Jan 24 '15 at 12:16
• @Gary'sStudent You could shorten your code. Forget checking the error number each time: Just add to the collection within the `On Error Resume Next` and `On Error GoTo 0` statements. Then, at the end, just do `CountUnique = c.count` – Ron Rosenfeld Jan 24 '15 at 12:22

It appears from your formula that your list contains only single-digit numbers. Here is one way to do it:

``````=10-ISERROR(FIND(0,B13))-ISERROR(FIND(1,B13))-ISERROR(FIND(2,B13))
-ISERROR(FIND(3,B13))-ISERROR(FIND(4,B13))-ISERROR(FIND(5,B13))
-ISERROR(FIND(6,B13))-ISERROR(FIND(7,B13))-ISERROR(FIND(8,B13))
-ISERROR(FIND(9,B13))
``````

I've broken up the formula onto separate lines for readability and to make the logic visible. If you want to copy and paste, you will need to delete the formatting carriage returns and extra spaces.

This starts with all ten digits (0 through 9), as possible unique values that could be present. It looks in the string for each digit. If it is not there, it reduces the number of possible unique values by one (unsuccessful FIND returns an error, making ISERROR true, which is handled by Excel as 1).

• This method is very clever. – Alex M May 21 at 23:24