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

microsoft excelworksheet-function

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.

Best Answer

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)

Related Question