Slow inner loop many to many matching

shwalk

Board Regular
Joined
Mar 8, 2011
Messages
61
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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
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?


<html><body>
<table border=1>
<tr><th colspan=2>Dataset 1</th><th colspan=2>Dataset 2</th></tr>
<tr><td>L2</td><td>L1</td><td>L2</td><td>L1</td></tr>
<tr><td>0100432</td><td>137054</td><td>CAT-0100757</td><td>20331</td></tr>
<tr><td>0100432</td><td>137054</td><td>CAT-0100760</td><td>20832</td></tr>
<tr><td>0100757</td><td>20331</td><td>CAT-0100761</td><td>19486</td></tr>
<tr><td>0100757</td><td>20331</td><td>CAT-0100765</td><td>19487</td></tr>
<tr><td>0100777</td><td>527201</td><td>CAT-0100766</td><td>497758</td></tr>
<tr><td>0100786</td><td>19495</td><td>CAT-0100768</td><td>19489</td></tr>
<tr><td>0100786</td><td>385080</td><td>CAT-0100769</td><td>19490</td></tr>
<tr><td>0100886</td><td>382621</td><td>CAT-0100777</td><td>527201</td></tr>
<tr><td>0100893</td><td>19530</td><td>CAT-0100777</td><td>527201</td></tr>
<tr><td>0100893</td><td>19530</td><td>CAT-0100783</td><td>19493</td></tr>
<tr><td>0100893</td><td>19530</td><td>CAT-0100786</td><td>19495</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100786</td><td>385080</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100786</td><td>385080</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100791</td><td>19496</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100791</td><td>19496</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100793</td><td>141941</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100793</td><td>141941</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100795</td><td>19498</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100795</td><td>19498</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100796</td><td>483494</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100796</td><td>483494</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100798</td><td>19500</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100803</td><td>19122</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100803</td><td>19122</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100805</td><td>372055</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100809</td><td>19503</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100842</td><td>19510</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100842</td><td>19510</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100849</td><td>19539</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100852</td><td>19514</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100854</td><td>26890</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100855</td><td>26862</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100858</td><td>26862</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100859</td><td>19516</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100859</td><td>19516</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100860</td><td>528460</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100860</td><td>528460</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100860</td><td>528460</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100860</td><td>528460</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100861</td><td>68176</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100862</td><td>61410</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100863</td><td>61410</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100875</td><td>19525</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100876</td><td>19526</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100884</td><td>26890</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100886</td><td>382621</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100891</td><td>26890</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100893</td><td>19530</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100893</td><td>19530</td></tr>
<tr><td>&nbsp</td><td>&nbsp</td><td>CAT-0100905</td><td>19534</td></tr>
</table>
</body></html>
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top