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.
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,
Put in cell
O123
This returns the part to the right of,
or-
if no,
Put in cell
P123
This returns1
if cellN123
is unique in the list, plus1
if cellO123
is uniqueCopy cells
N123..P123
down toN123..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)