Excel – unique count of values in a cell separated by comma in excel

microsoft excelmicrosoft-excel-2010

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.

Best Answer

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:

ALT-F11 brings up the VBE window
ALT-I ALT-M opens a fresh module
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:

bring up the VBE window as above
clear the code out
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

Related Solutions

Excel – How to count unique comma-separated values in Excel 2010

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)

Best Answer

Related Solutions

Excel – How to count unique comma-separated values in Excel 2010

Related Question