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

1. ## 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?

2. ## Re: Faster if/countif formula for flagging uniques - countif too slow

Hi,

Can you not use the INDEX option available under "Value Field Settings" of the Pivot Table if you are just trying to calculate the Unique Counts?

3. ## Re: Faster if/countif formula for flagging uniques - countif too slow

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

4. ## Re: Faster if/countif formula for flagging uniques - countif too slow

See the snapshot below, check if this option is available in your office version. I have also attached an example file.

5. ## Re: Faster if/countif formula for flagging uniques - countif too slow

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1