2

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.

4
  • 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
4

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
        c.Add a, CStr(a)
        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)

To learn more about macros in general, see:

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:

enter image description here

4
  • 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
  • 1
    @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
2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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