Count unique values in column B associated with each value in column A

[alienss]

Hey,

I currently have a table with two columns:

John	siteA
Mike	siteA
John	siteC
Dan	siteC
Mike	siteA
Dan	siteC
Mike	siteB
Mike	siteB
John	siteA
Dan	siteC

I want to create a third column that could list the count of unique sites attributed to each name. The logic would read like this:

John	2 unique sites
Mike	2 unique sites
Dan	1 unique site

So basically, the final table would look something like this:

John	siteA	2
Mike	siteA	2
John	siteC	2
Dan	siteC	1
Mike	siteA	2
Dan	siteC	1
Mike	siteB	2
Mike	siteB	2
John	siteA	2
Dan	siteC	1

I currently use an array formula to do this, but having a table of 30K rows, it is incredibly slow (took me 10 minutes to get to about 3%):

Please Login or Register  to view this content.

Is there any other formula that would help me do the job faster?

Cheers

[benishiryo]

hi alienss, i would normally use somethin similar to your array formula too. but i dont usually have 30K rows. no guarantees, but you can try these:

COUNTIFS not an array, so i guess it works a little faster

Please Login or Register  to view this content.

or SUMPRODUCT, which works faster than an array:

Please Login or Register  to view this content.

let me know how it turns out

[alienss]

Hey benishiryo,

Tried both of them, but they only seem to count to a maximum of 2. For example, in my initial table, if I add John -> siteB, it doesn't count 3 unique sites, still counts only 2 (but it works if I add Dan -> siteB, cause Dan only had 1 site).

Any idea why this might be?

[benishiryo]

ahhh that's cause i only catered for 2. tried to help you search for a non-array formula, but to no avail. would you consider using a helper column?
place either of this in Column C:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

then in column D:
=SUMIF($A$1:$A$10,A1,$C$1:$C$10)