Results 1 to 11 of 11

Increase speed for match formula on over 130k rows

Threaded View

  1. #1
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Increase speed for match formula on over 130k rows

    I have a dataset which is a little over 130k rows. Right now I run a match on the dataset against a master file (Column A) to return a true/false. All of this is in the same workbook.

    =ISNUMBER(MATCH(A2,Data!A:A,0))
    If I run this formula on all recordd it takes about 20 minutes to run so I have been splittling the dataset up into smaller more manageble pieces.

    Just found out today I need to add another match and compare 2 columns (A & B) to return true/false.

    =ISNUMBER(MATCH(A2&B2,INDEX(Data!A:A&Data!B:B,0),0))
    Is there some way to speed this process up so I can run it on all 130k at a time?

    Also, been trying but can't seem to figure it out, but is there a way to incorporate the method were you search for where the data starts, then find the length and use all this information in an Offset construct.

    So, instead of searching all 130k records you narrow the search and now only query against maybe 10k. Also, with Offset being volatile will this just add to the processing time.

    Sample attached, but just contains a small amount of data.
    Attached Files Attached Files
    Last edited by jeffreybrown; 09-24-2010 at 08:13 PM.

Thread Information

Users Browsing this Thread

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

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