Faster if/countif formula for flagging uniques - countif too slow

Hello. I have a spreadsheet of about 100k rows and need a formula to provide a 1 for the first unique value encountered in order to accurately feed into a pivot table for number of unique values.

The formula currently in use is

Please Login or Register  to view this content.

Unfortunately, with the number of rows I have in this sheet, this formula basically just crashes excel whenever I try to change anything.

I have tried

Please Login or Register  to view this content.

as well which is certainly faster, but still very slow


I am open to VBA solutions as well.


Ideas?

I see no such option? I am using Excel 2010 x64

If you are still looking for a formula solution have you tried FREQUENCY / MATCH? I have never tried FREQUENCY / MATCH over that many rows. It is unexpectedly fast. It would have to be array committed over the entire range at once though.

Something like this in column C.


=IF(FREQUENCY(MATCH($B$1:$B$100000,$B$1:$B$100000,0),ROW($B$1:$B$100000)),1,"")


I'd be curious to hear how this does over that many rows.

Edit: Curiosity got the best of me. I tried it with 100,000 rows of junk data. It took less than 3 seconds.

Update: This speed seems to depend on the complexity and number of unique items in the list.