Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Slow inner loop many to many matching

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Slow inner loop many to many matching

    Is there a faster way to do many to many matching of data in 2 worksheets than using two loops like ....
    For r = 2 to r2
    For rr = 2 to rr2
    ...
    Next rr
    Next r

    Gets quite slow when worksheets have many thousands rows to compare. Would like to avoid using Access database if possible. Any ideas?

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Slow inner loop many to many matching

    Can't you use the Match WorksheetFunction? You may find this discussion interesting:

    Daily Dose of Excel Blog Archive Find Matching Data in Array Speed Test
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Slow inner loop many to many matching

    Thanks for the reply. But I don't want to stop at the first match.

  4. #4
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,534
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Slow inner loop many to many matching

    Do you have a shared, or primary key, that links the data?
    I have found that with LOADS of data, it is quicker to synchronise the data with either a sumif, or vlookup, out of both tables, and then do an if statement beside it, if 1 = 2 then "", "Check" sort of thing.
    Say you're taking inventory, and wanting to see changes to MOQ's. Use the product code as the primary key, and do a sumif off it to obtain the was value to the want value. If you were wanting to compare sales managers, you would need a vlookup.
    I have tried macros too, but so far, this is much much faster.
    I hope that helps.

  5. #5
    Board Regular
    Join Date
    Mar 2011
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Slow inner loop many to many matching

    Thanks for your ideas. Haven't been able to apply it to my scenario as yet. For now I found that sorting each data set, using Exit For after matching has finished for a given value, and setting the inner loop (rr) to restart where it left off, makes it run faster. Unfortunately I'm not convinced this is a reliable approach. Below is some sample data. I use both L1 and L2 to match the 2 datasets (then extract out other columns not shown). The problem is that L2 is not unique for each L1, so is there a risk of the Exit For skipping matches?


    ******>





















































    Dataset 1Dataset 2
    L2L1L2L1
    0100432137054CAT-010075720331
    0100432137054CAT-010076020832
    010075720331CAT-010076119486
    010075720331CAT-010076519487
    0100777527201CAT-0100766497758
    010078619495CAT-010076819489
    0100786385080CAT-010076919490
    0100886382621CAT-0100777527201
    010089319530CAT-0100777527201
    010089319530CAT-010078319493
    010089319530CAT-010078619495
    &nbsp&nbspCAT-0100786385080
    &nbsp&nbspCAT-0100786385080
    &nbsp&nbspCAT-010079119496
    &nbsp&nbspCAT-010079119496
    &nbsp&nbspCAT-0100793141941
    &nbsp&nbspCAT-0100793141941
    &nbsp&nbspCAT-010079519498
    &nbsp&nbspCAT-010079519498
    &nbsp&nbspCAT-0100796483494
    &nbsp&nbspCAT-0100796483494
    &nbsp&nbspCAT-010079819500
    &nbsp&nbspCAT-010080319122
    &nbsp&nbspCAT-010080319122
    &nbsp&nbspCAT-0100805372055
    &nbsp&nbspCAT-010080919503
    &nbsp&nbspCAT-010084219510
    &nbsp&nbspCAT-010084219510
    &nbsp&nbspCAT-010084919539
    &nbsp&nbspCAT-010085219514
    &nbsp&nbspCAT-010085426890
    &nbsp&nbspCAT-010085526862
    &nbsp&nbspCAT-010085826862
    &nbsp&nbspCAT-010085919516
    &nbsp&nbspCAT-010085919516
    &nbsp&nbspCAT-0100860528460
    &nbsp&nbspCAT-0100860528460
    &nbsp&nbspCAT-0100860528460
    &nbsp&nbspCAT-0100860528460
    &nbsp&nbspCAT-010086168176
    &nbsp&nbspCAT-010086261410
    &nbsp&nbspCAT-010086361410
    &nbsp&nbspCAT-010087519525
    &nbsp&nbspCAT-010087619526
    &nbsp&nbspCAT-010088426890
    &nbsp&nbspCAT-0100886382621
    &nbsp&nbspCAT-010089126890
    &nbsp&nbspCAT-010089319530
    &nbsp&nbspCAT-010089319530
    &nbsp&nbspCAT-010090519534


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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