Unique Records

[Anthony]

Hi

Hoping someone might be able to help.

I currently use the formula
=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1) from the Contextures
website to count unique records in data sets, where a record is
repeated in two or more rows. I create an additional column as
required, and use this field as my count data. Works a treat.

However I now have a large dataset (400000 records) for which this is
incredibly slow and painful. Is it possible to create a macro to do the
same thing, and if so how do I do this and will this speed things up?

Thanks in advance.

Cheers, Anthony

 

[Bob Phillips]

=SUMPRODUCT(--($A$2:$A2000<>""2)/COUNTIF($A$2:$A2000,$A$2:$A2000&""))

will count the total unique items

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bernard Liengme]

Bob, please explain the need for
a) the 2 in ""2
and
b) the need for &""
best wishes

 

[Bob Phillips]

The &"" is easy. It is used to avoid a otherwise you get a #DIV/0 error if
there are any blank cells. The test for blanks is used to avoid counting
blanks as a unique item.

The 2 is not so easy, well actually it is, it is a typo, it shouldn't be
there.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)