+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    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?
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    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. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

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

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

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    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.
    Attached Images Attached Images
    Attached Files Attached Files

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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.
    Last edited by FlameRetired; 08-20-2015 at 04:45 PM.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Faster way to COUNTIF across multiple columns for datesets over 100,000 rows
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-15-2014, 05:44 PM
  2. faster way to do countif via vba for large dataset
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-10-2014, 09:58 PM
  3. [SOLVED] Slow Calculations - COUNTIF
    By gray-ish in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-16-2013, 05:05 PM
  4. [SOLVED] IF & COUNTIF? Flagging up dependent equipment
    By 3smees23 in forum Excel General
    Replies: 26
    Last Post: 05-27-2012, 05:20 AM
  5. [SOLVED] SUMPRODUCT/COUNTIF being very slow and crashing
    By Sordini in forum Excel General
    Replies: 9
    Last Post: 05-08-2012, 12:04 PM
  6. Countif's of uniques > number of total
    By starstill in forum Excel General
    Replies: 8
    Last Post: 04-22-2010, 06:10 PM
  7. Slow down using COUNTIF
    By solnajeff in forum Excel General
    Replies: 2
    Last Post: 12-16-2007, 03:06 PM

Bookmarks

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