Efficiently apply a VLOOKUP to a large range using VBA

Kickflip

New Member
Joined
Sep 24, 2014
Messages
15
Hey!

Hoping someone could answer this for me!

I have a data set comprised of 2 sheets of around 500,000 rows of data each in a single workbook.

The two sheets are the same report, both containing a unique identifier in column A and a corresponding status value in column B (which can only be 1 of 4 different values) however one sheet is old data the other is new data. The purpose is to highlight changes in status between the old data and new. The new data is being automatically exported daily from an SAP query tool which overwrites the previous export.

So, I have a macro that is built into a Macro enabled workbook template. It removes the old data sheet then takes the previous ‘new’ data and moves that to the ‘old’. It then imports the new data from the SAP export and creates a new ‘new data’ sheet. In other words it is continually cycling data from new to old.

In the outset I set up the workbook using data connections so when the new sheet is added, the data is pulled in from the new extract ready to conduct the next step which is the comparison. The trouble with this method is because the data set is so large the process of importing it is very slow, so I opted instead for a quicker option of instructing the workbook to open the new data file and simply moving the sheet into the template.

Once the new data is in place, the code will drop a vlookup (nested within an IF statement) in cell C2 in the ‘old’ data sheet, then a fill down command is used to copy the formula down for all rows.

This is the problem. The code works fine, except that it takes a very long time to process, like 10 mins or so.

My question is whether using a fill down is the most efficient means of applying a VLOOKUP to such a large range or whether there could be an alternative to shave off a few mins of processing time!

The pc has an i5 processor with 16gb RAM so should be ok to handle this stuff, and I’d expect some delay using a set this large, but I just wondered if it could be speeded up by using and alternative means?

Any ideas?

Thanks so much!
 
Last edited:

Some videos you may like

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"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
What you are essentially describing is a relational database (two tables related by a common join field).
If you want to do it more efficiently with a large data set like you have, use a database program instead, like Access (or MySQL, SQL, Oracle, etc).
They handle this stuff much more efficiently than Excel, as that is what they were designed for.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
There are a few methods that are quicker than a VLOOKUP.

Index/match would typically run much faster and is fairly simple.
Using VBA with a dictionary - requires a bit more work and has some limitations but is much faster.

There is also another method which I haven't used, however I have known other people to use, that is a double VLOOKUP.
Finally if you are using Excel 2013 + you may be able to do it using Data model to get what you are looking for.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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
Top