Return Number of Unique Values

  • Thread starter Thread starter shaileshsachdeva
  • Start date Start date
S

shaileshsachdeva

Hi,
Have the following input:
A ON
A PQ
A ON
B NY
B LA
B CA
B CA
B ON

Need to return the number of unique values assigned to A and B:
A = 2
B = 4

How can I do this without VB? Array formulas?

Thanks.
 
=SUM(--(FREQUENCY(IF(A2:A100="B",MATCH(B2:B100,B2:B100,0)),ROW(INDIRECT("1:"&ROWS(B2:B100))))>0))

as an array formula, commit wit Ctrl-Shift-Enter, not just Enter.
 
Try

=SUM(IF(FREQUENCY(IF(A1:A8="A",IF(B1:B8<>"",MATCH(B1:B8,B1:B8,0))),ROW(A1:A8)-ROW(A1)+1),1))

confirmed with CTRL+SHIFT+ENTER
 
(e-mail address removed) wrote...
Have the following input:
A       ON
A       PQ
A       ON
B       NY
B       LA
B       CA
B       CA
B       ON

Need to return the number of unique values assigned to A and B:
A = 2
B = 4
Click to expand...
...

If there are no blank cells in the second column, you could do this
without even array formulas. The following may be entered normally.

=COUNT(1/FREQUENCY((A1:A8="A")*MATCH(B1:B8,B1:B8,0),
ROW(B1:B8)-MIN(ROW(B1:B8))))-1

If there could be blank cells in the second column, array formulas
become necessary. Another alternative,

=COUNT(1/MMULT((A1:A8=D1)*(1-ISBLANK(B1:B8))*(B1:B8=TRANSPOSE(B1:B8)),
ROW(B1:B8)^0))
 
Back
Top