Unique count in two Columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I would be grateful if someone could help me, I have been reading posts about counting unique numbers in a list, however I want to count unique's within one column, but triggered from another column. Here is an example

Col 1 Col
abc
def
abc
abc
ghi
abc
def

I want to achive an answer of 2, which would be achaived from the fact that
The value in column 2 must be higher than 0 and if that figure is higher than 0 then count the unique records in col 1. Therefore the 'abc' and 'def' both have correspong data greater than 0 but 'ghi' is not greatrer than 0. Thus the number of unique records =
 
Hi
try the array formula
=COUNT(1/FREQUENCY(IF(B1:B10>0,MATCH(1:A10,A1:A10,0)),
ROW(INDIRECT("1:"&COUNTA(A1:A10)))))
-----Original Message-----
Hi,

I would be grateful if someone could help me, I have been
Click to expand...
reading posts about counting unique numbers in a list,
however I want to count unique's within one column, but
triggered from another column. Here is an example :
Col 1 Col 2
abc 0
def 2
abc 3
abc 4
ghi 0
abc 0
def 2

I want to achive an answer of 2, which would be achaived from the fact that :
The value in column 2 must be higher than 0 and if that
Click to expand...
figure is higher than 0 then count the unique records in
col 1. Therefore the 'abc' and 'def' both have correspong
data greater than 0 but 'ghi' is not greatrer than 0. Thus
the number of unique records = 2
 
try the array formula
=COUNT(1/FREQUENCY(IF(B1:B10>0,MATCH(1:A10,A1:A10,0)),
ROW(INDIRECT("1:"&COUNTA(A1:A10)))))
Click to expand...
...

Typo in the 1st argument to MATCH: 1:A10 should be A1:A10.

Another alternative, also an array formula,

=SUM((B1:B10>0)/((B1:B10=0)+MMULT(--(IF(B1:B10>0,A1:A10)=TRANSPOSE(A1:A10)),
--(B1:B10>0))))
 
Harlan said:
...
..

Typo in the 1st argument to MATCH: 1:A10 should be A1:A10.

Another alternative, also an array formula,

=SUM((B1:B10>0)/((B1:B10=0)+MMULT(--(IF(B1:B10>0,A1:A10)=TRANSPOSE(A1:A
10)),
--(B1:B10>0))))
Click to expand...

Hi Harlan
thanks for the typo correction :-)
Just curious: Any preference for one of the solutions (e.g.
performance, robustness, etc.)

Regards
Frank
 
...
Just curious: Any preference for one of the solutions (e.g.
performance, robustness, etc.)
Click to expand...

Performance: 7 vs 4 function calls.
Flexibility: 5 vs 2 function call nesting levels.

The IF(B1:B10>0,MATCH(A1:A10,A1:A10,0)) expression is more efficient than the
MMULT(--(IF(B1:B10>0,A1:A10)=TRANSPOSE(A1:A10)),--(B1:B10>0)) expression, but
this is offset by the SUM((..)/((..)+MMULT(..))) expression being more efficient
than the COUNT(1/FREQUENCY(IF(..),ROW(..))) expression due to the former being
one big but linear time calculation vs the implicit sorting needed with
FREQUENCY.
 
Harlan Grove said:
Performance: 7 vs 4 function calls.
Flexibility: 5 vs 2 function call nesting levels.

The IF(B1:B10>0,MATCH(A1:A10,A1:A10,0)) expression is more efficient than the
MMULT(--(IF(B1:B10>0,A1:A10)=TRANSPOSE(A1:A10)),--(B1:B10>0)) expression, but
this is offset by the SUM((..)/((..)+MMULT(..))) expression being more efficient
than the COUNT(1/FREQUENCY(IF(..),ROW(..))) expression due to the former being
one big but linear time calculation vs the implicit sorting needed with
FREQUENCY.
Click to expand...


Hi Harlan
as always thanks for this detailed answer!

Best regards
Frank
 
Back
Top