Solved

Match vs Loop

Posted on 2014-03-15
3
405 Views
Last Modified: 2014-03-16
I am curious of performance from doing a loop vs a Match function.  If I have a worksheet lets called parts with 10,000 rows and 15 columns.  I have another worksheet in the same workbook lets call regional daily orders.  If every day I review every row in the regional daily orders worksheet (daily row count of 500) for a column called part number and find the corresponding  part number in the worksheet called parts and store the value price and manufacture is this better accomplished with a loop or match function or something else.

I am pretty familiar with loops, but I do not want this to be a case where I am doing a loop for comforts sake...if there is a better method then I would like to learn..
0
Comment
Question by:upobDaPlaya
3 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 250 total points
ID: 39931699
Without more information, a MATCH function in a formula will most definitely be faster than a VBA loop doing the same. Excel's built in functions are optimized compiled code that have access to many more underlying data structures than we do using VBA. They are optimized for performance while VBA is a more generalized and interpreted automation language.

Kevin
0
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 250 total points
ID: 39931704
As Kevin said much better than I can, I think the most straightforward way, and potentially the fastest, is to run a vlookup (or a match) in the worksheet, then paste the values, e.g.

sheets("Orders").range("E2:E6500").formular1c1="=vlookup(rC2,Parts!R1C1:R150000C15,3,0)"
sheets("Orders").range("E2:E6500").value=sheets("Orders").range("E2:E6500").value

Open in new window


then do the same for the manufacturer.

Thomas
0
 

Author Closing Comment

by:upobDaPlaya
ID: 39933418
I fooled around with a match function today...wish I knew of it earlier :)
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question