Unique Records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Anthony - 17 Oct 2006 11:05 GMT

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

Reply to this Message

Bob Phillips - 17 Oct 2006 13:55 GMT

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

will count the total unique items

Signature

HTH

Bob Phillips

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

> Hi
>
[quoted text clipped - 13 lines]
>
> Cheers, Anthony

Reply to this Message

Bernard Liengme - 17 Oct 2006 14:34 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> =SUMPRODUCT(--($A$2:$A2000<>""2)/COUNTIF($A$2:$A2000,$A$2:$A2000&""))
>
[quoted text clipped - 17 lines]
>>
>> Cheers, Anthony

Reply to this Message

Bob Phillips - 17 Oct 2006 14:49 GMT

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.

Signature

HTH

Bob Phillips

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

> Bob, please explain the need for
> a) the 2 in ""2
[quoted text clipped - 22 lines]
> >>
> >> Cheers, Anthony

Reply to this Message