+ Reply to Thread
Results 1 to 14 of 14

Matches for 50,000 rows

  1. #1
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Matches for 50,000 rows

    Hello. I'm trying to do a match formula for 400,000 rows. Is there a better formula? if(match(h2,$d$2:$d$420,0),yes, no). Thanks.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Matches for 50,000 rows

    Are the search values sorted in either ascending or descending order? If they are or can be sorted, then you can use a much more efficient binary search algorithm by specifying the 3rd argument as 1 (for data sorted ascending) or -1 (for data sorted descending).

    If they are not and cannot be sorted, then that might be as good as it gets in Excel. I don't know if a more robust database program might be able to perform the same linear search faster than Excel.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Matches for 50,000 rows

    You would normally use ISNUMBER with that, and you can use full-column references, so try this:

    =IF(ISNUMBER(MATCH(H2,$D:$D,0)),"yes","no")

    You could also do it this way:

    =IF(COUNTIF($D:$D,H2),"yes","no")

    Hope this helps.

    Pete

  4. #4
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Matches for 50,000 rows

    @ Mr. Shorty. I sorted into descending order and then wrote this =IF(ISERROR(MATCH(H288804,$D$2:$D$466271)),0,1). It works faster but it's not getting the matches right. It's returning 0's when there are in fact matches.

  5. #5
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Matches for 50,000 rows

    Hi Peter. Those formulas work but they don't fix the speed problem. It bogs my excel down to where it doesn't function.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Matches for 50,000 rows

    Review help file for MATCH() function: https://support.office.com/en-us/art...9-533f4a37673a Note that, if the optional 3rd argument is not specified, that it assumes a value of 1 for that argument, which only works for data sorted in ascending order. If you want to have your data sorted in descending order, be sure to specify -1 for that argument.

  7. #7
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Matches for 50,000 rows

    @ Mr. Shorty. Got it. Still incredibly slow unfortunately

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Matches for 50,000 rows

    I suspect you're still not using the correct formula.
    Entia non sunt multiplicanda sine necessitate

  9. #9
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Matches for 50,000 rows

    Try this:
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Matches for 50,000 rows

    Pretty sure that I am. I used Pete_UK formula, put both columns into descending order, and then used a -1 for the third option. There are 400,000 rows.

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Matches for 50,000 rows

    I suspect something else then. I can fill an entire column (1E6 rows) in descending order and my MATCH() function is near instantaneous. Are you certain the search is the bottleneck, or could there be something else slowing your sheet down?

  12. #12
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Matches for 50,000 rows

    @Mr. Shorty, would you mind attaching your spreadsheet for me to check? Thanks.

  13. #13
    Forum Contributor
    Join Date
    09-30-2015
    Location
    LA my baby
    MS-Off Ver
    2013
    Posts
    727

    Re: Matches for 50,000 rows

    This is the formula I'm using =IF(ISNUMBER(MATCH(F466300,$A$2:$A$463252,-1)),1,0)

    Data is descending in both columns.

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Matches for 50,000 rows

    I don't have Excel on this computer, but it is a very easy spreadsheet to build from scratch.

    1) Start with a new sheet
    2) In A1 enter =ROW(A$1048576)
    2a) In A2 enter =A1-1
    3) Copy/fill A2 down to the bottom of the column.
    3a) Or any other formula combination that will give you a descending list of numbers in column A
    4) In C1 enter =RANDBETWEEN(0,1050000)
    5) In D1 enter =MATCH(C1,A1:A1048576,-1)
    5a) If desired, include the IF(ISNUMBER(...)...) parts of the function, since they should contribute insignificantly to the computation time.

    Press F9 and see how long it takes to calculate.

+ 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. change background if two rows matches
    By Ahmad.mian in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-13-2016, 07:34 PM
  2. [SOLVED] Copy rows when value matches a value in another column
    By abigail99 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-28-2014, 04:18 PM
  3. [SOLVED] Find value in a table that matches same value in rows
    By anonymousny in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2013, 05:14 PM
  4. Highlight rows if A matches but B is different
    By SpeedyOne18 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-07-2013, 08:05 PM
  5. Replies: 3
    Last Post: 01-31-2013, 04:33 AM
  6. [SOLVED] Delete rows if any cell in Sheet 1 rows matches any cell in Sheet2 column
    By newbie88 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2012, 10:26 AM
  7. Compare rows, cut matches to sheet, delete original rows
    By somesoldiers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2010, 11:42 AM

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