+ Reply to Thread
Results 1 to 2 of 2

Faster way to COUNTIF across multiple columns for datesets over 100,000 rows

  1. #1
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Faster way to COUNTIF across multiple columns for datesets over 100,000 rows

    I'm SURE there's a better way to do this - hoping one of you can help point me to the right function, formula or approach.

    I have spreadsheets being loaded with data by automated process on a daily basis. That data can be anywhere from 5 to over 1 million rows. Generally, we expect about 50,000 rows though.

    Among other validations being done, I need to be sure that there are no duplicates across 4 specific columns. However, I cannot DELETE them - I have to flag them with an error message.

    Currently, I'm doing this by creating a key like FIELD1.FIELD2.FIELD3.FIELD4 for each row.
    I then do a COUNTIF through the range of data on that value, and if the result is > 1, I replace with an error message.

    The calculation is taking far too long though.
    - I have turned off screen updating
    - I have modified calculation to be done manually

    Is there something I can do instead of using COUNTIF, as that processing seems to be killing the performance? Takes just north of 1 minute for 60,000 rows.

    Please Login or Register  to view this content.
    Thanks for any guidance!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Faster way to COUNTIF across multiple columns for datesets over 100,000 rows

    Suggest you post a sample workbook with some sample data.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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 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
  2. Replies: 12
    Last Post: 01-30-2014, 01:33 AM
  3. Replies: 9
    Last Post: 12-15-2013, 10:05 PM
  4. [SOLVED] Faster way to copy and paste multiple columns in Sheet1 to corresponding columns in Sheet2
    By babbeleme1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-19-2013, 12:55 AM
  5. Replies: 2
    Last Post: 04-18-2013, 02:03 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