# Match data in 1 column and count unique data in another column based on matches

1. ## Match data in 1 column and count unique data in another column based on matches

Hello,

I was curious if anyone would have an alternate formula to use to match data in one column and count unique data in another column that wouldn't require an Array? I found the array formula below and it works, but my worksheet has 60,000 rows and this formula takes forever to calculate, which makes it impossible to use. I was hoping for an alternative formula that may not require as much computer resources and could provide the results in a timely fashion. Appreciate any suggestions/alternative formulas!

Array formula: ``Please Login or Register  to view this content.``  Register To Reply

2. ## Re: Match data in 1 column and count unique data in another column based on matches

given version (XL2013) and the sample data (where A is seemingly sorted Asc) then you might try switching to:

C3:
=IF(\$A3=\$A2,\$C2,SUMPRODUCT(1/COUNTIF(\$B3:INDEX(\$B:\$B,MATCH(\$A3,\$A:\$A)),\$B3:INDEX(\$B:\$B,MATCH(\$A3,\$A:\$A))&"")))
copied down

the above (c/o Col A being sorted)

a) only calculates the unique number once per item A (the other A items use the previously calculated result)
b) switches from multi criteria COUNTIFS to single criteria -- this has a big impact on performance (for COUNTIFS - specifically) aside the most recent XL versions (which fix the 'bug')
c) limits the range of each COUNTIF calc such that it very small {c/o binary search based MATCH}

so, with the above changes, you should find the performance improves a fair bit when applied to a large dataset  Register To Reply

3. ## Re: Match data in 1 column and count unique data in another column based on matches

=COUNT(1/FREQUENCY(IF(\$A\$3:\$A\$40000=A3,MATCH(\$B\$3:\$B\$40000,\$B\$3:\$B\$40000,)),ROW(A\$3:A\$40000)-ROW(\$A\$3)))

Ctrl+Shift+Enter  Register To Reply

4. ## Re: Match data in 1 column and count unique data in another column based on matches

D3 , Array formula , Copy and drag down

HTML Code:  Register To Reply

5. ## Re: Match data in 1 column and count unique data in another column based on matches

Thanks XLent, Bo_Ry & wk9128 Really appreciate your formula suggestions! Thanks so much!  Register To Reply