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%):
=SUM(IF(FREQUENCY(IF(A$2:A$38581=A2;MATCH(B$2:B$38581;B$2:B$38581;0));ROW(B$2:B$38581)-ROW(B$2)+1);1))
Is there any other formula that would help me do the job faster?
Cheers
Bookmarks