0 votes
in VBA by Beginner (2 points)

 i have employee codes in the second column of new data and second column of old data. this is my unique identifier which i use to look up.

I want to use the employee code in new data and look up for the same in old data and fetch some information.

I am using the below code, however it takes a lot of time for large data sets. i have identified that this is mainly due to vlookup function.

it would be great if there is some alternative which is quicker.

Dim LR As Long, i As Long, LR1 As Long
LR = Range("A7") + 11  'row count for the new data
LR1 = Range("L7") + 11 'row count for the old data

'naming old data table
Range("M12:Q12").Select
Range(Selection, Selection.End(xlDown)).Name = "DataTable"

'naming new data table
Range("B12:C12").Select
Range(Selection, Selection.End(xlDown)).Name = "DataTable1"

For i = 12 To LR
On Error Resume Next
    Range("Y" & i).Value = CDate(Application.WorksheetFunction.VLookup(Cells(i, 2), Range("DataTable"), 3, False))
    If Range("Y" & i).Value = "" Then
    Range("Y" & i).Value = "New"
    End If
    Range("Z" & i).Value = CDate(Cells(i, 4)) - Cells(i, 25)
    If Range("Z" & i).Value = "" Then
    Range("Z" & i).Value = "New"
    End If
    Range("AA" & i).Value = CDate(Application.WorksheetFunction.VLookup(Cells(i, 2), Range("DataTable"), 4, False))
    If Range("AA" & i).Value = "" Then
    Range("AA" & i).Value = "New"
    End If
    Range("AB" & i).Value = CDate(Cells(i, 5)) - Cells(i, 27)
    If Range("AB" & i).Value = "" Then
    Range("AB" & i).Value = "New"
    End If
    Range("AC" & i).Value = (Application.WorksheetFunction.VLookup(Cells(i, 2), Range("DataTable"), 5, False))
    If Range("AC" & i).Value = "" Then
    Range("AC" & i).Value = "New"
    End If
    Range("AD" & i).Value = ((Cells(i, 6)) / Cells(i, 29)) - 1
    If Range("AD" & i).Value = "" Then
    Range("AD" & i).Value = "New"
    End If
    y1date = Cells(i, 4).Value    'DOB prev
    If (DateDiff("d", y1date, mydate) / 365) >= Cells(i, 8).Value Then
    Cells(i, 36).Value = 1
    Else: Cells(i, 36).Value = 0
    End If
    'Potential
    If (Cells(i, 23).Value) >= (Cells(i, 8).Value - 1) Then
    Cells(i, 41).Value = 1
    Else: Cells(i, 41).Value = 0
    End If
    
 Application.StatusBar = Round(((0 * (LR - 9) + (i - 9)) / (6 * (LR - 9))) * 100, 0) & " % " & "Completed "
Next i

Thanks in advance!

2 Answers

0 votes
by Super Expert (2.4k points)

VBA is notoriously slow accessing a range directly in a macro, so it makes sense that doing it in a loop with a a vlookup worksheet function would be slow. To maximimize performance, you want to minimize the amount of times you reference a cell or range inside a For Loop. Using Variables, Arrays and Dictionaries are a few ways to do that. Anywhere you can replace a Range(...) or Cells(...) with a variable will speed up your macro immensely.

Another great way to speed up a macro for large data sets is to set Application.ScreenUpdating=False. There are other application level properties you can change to make the macro even faster. We outline the other ways to speed up your macro in this tutorial. Take a look at this and see if the speed is acceptable for you.

EDIT: Read Steps 1 through 4 in my comments below to show how using variables instead of ranges in your expressions can reduce macro speed by over 67%. Read the comments for these faster solutions before reading the Range.Find solution, which is also slow.

One alternative to using VBA VLookup is to use Range.Find, but my demonstrations show it's 7x slower than VLookup. For completeness, I'll provide it here anyway. These 2 expressions are equivalent:

'with VLookup'
    Range("F" & i) = Application.WorksheetFunction.VLookup(Range("E" & i), Range("A2:C9"), 2, False)
'with Range.Find'
    Range("F" & i) = Range("A2:C9").Find(what:=Range("E" & i)).Offset(0, 1)

Both of these expressions look for the value in column E (and row i) in the range "A2:C9." It then enters the value it wants from the second column of the table in to Column F. The Range.Find method does this by using VBA Offset to offset the found expression by (0,1), or 0 rows, 1 column from the cell it found. Although it's kind of cool, Range.Find is much slower.

Another option would be to read your table into a VBA dictionary with the key for each item in the dictionary set to the item you want to look up. If you have it set properly, you could use the .Exists method to search for related elements without having to use Range.Find or VLookup. You could also read the range into an Array and loop through until you find a match, but there's a break-even point where that would be slower, too

by Beginner (2 points)
Thanks a lot for your suggestion. However, it works slower than Vlookup.

I have already incorporated the ScreenUpdating option and the others in order to speed up the macro.

Do let me know in case there is some other quicker alternative for VlookUp.

 

Thank you once again!
by Super Expert (2.4k points)

Your best bet is to read your range into an array or a dictionary. VBA is notoriously slow accessing a range directly in a macro, so it makes sense that doing it in a loop using vlookup would be slow. To maximimize performance, you want to minimize the amount of times you reference a cell inside a For Loop. Using Arrays and Dictionaries are a couple ways to do that

Array Method: After storing your data range into an array using the link above, you could use a function like this one to search for and return the position of the element in an array. . Arrays will probably be the easiest to implement, but dictionaries might offer slightly better performance. It's hard to say, really.

Dictionary Method: For the dictionary, it sounds like you'd want to set up the values in your 2nd column Cells(i, 2) as your "key" and store the rest of the values in the corresponding row as the "item." You would then use the .Exists method to quickly locate the cell in the dictionary.

by Beginner (2 points)
Thanks again!

However I have not worked with dictionaries before.

Could you please help me with the code for setting my second column as the key?

Also, I was going through the link you provided. there are two arguments to the function dict.Add. one is Key and other is Item. What is Item for?

 

Thanks!
by Super Expert (2.4k points)

A rewrite to support a dictionary would be extensive. Before we do that, let's try to rewrite your code using variables. My tests show replacing Range("DataTable") in all your vlookup formulas using the steps below increases performance (decreases speed) by 20-25% (steps 1 and 2 below). Step 3 reduces speed by another 50%.

Step 1:

After your line of code

Range(Selection, Selection.End(xlDown)).Name = "DataTable"

add the following code:

    Dim wsDataTable As Range
    Set wsDataTable = Range("DataTable")

Step 2:

Replace all instances of Range("DataTable") in your VLookup formulas with wsDataTable.

Step 3:

Your code repeats lines like:

    Range("Y" & i).Value = YourVLookupFormula
    If Range("Y" & i).Value = "" Then
    Range("Y" & i).Value = "New"
    End If

You should change the code so it writes the values to a variable then checks the status of that variable before changing it's value. Something like this, for example:

    MyString = YourVLookupFormula
    If MyString = "" Then
    MyString = "New"
    End If
    Range("Y" & i).Value = MyString

Optional Step 4:

The change above decreases speed quite a bit on my demos. To shave more time off, you could store the value Cells(i, 2) to a string and replace Cells(i, 2) in your VLookup formulas with the string variable name, like str1.

Again, directly referencing cells and ranges in a macro significantly slows it down so anything you can do to minimize that (by replacing them with variables) will help reduce speed. 

--------------------------------

Dictionaries:

For the dictionary solution to work, you'd need to set the values you're looking up as the "key" and the rest of the data table for that row as the "Item." For example, if you're looking up values in the 2nd column of the DataFrame, you would right your Dictionary macro like this:

Sub DictionaryVLookupAlternative()
    ' (1) set up the VBA dictionary object (Tools > References > Microsoft Scripting Runtime)
    ' (2) Add the values from the column you want to vlookup as your "key"
    ' (3) Add the corresponding rows of data as an array for each item
    Application.ScreenUpdating = False
    Dim dict As Scripting.Dictionary
    Dim LR As Long, i As Long
    Dim str1 As String
    Set dict = New Scripting.Dictionary
    LR = Range("A7") + 11

    'naming old data table
    Range("M12:Q12").Select
    Range(Selection, Selection.End(xlDown)).Name = "DataTable"
    Dim wsDataTable As Range
    Set wsDataTable = Range("DataTable")

'add each row of table to dictionary with the entire row as the dictionary "ITEM"
'and the cell you want to lookup as the "KEY"
    With wsDataTable
    For i = 1 To .Rows.Count
        dict.Add .Cells(i, 2).Value, Range(.Cells(i, 1), .Cells(i, .Columns.Count)).Value2
    Next i
    End With
    
    For i = 12 To LR
        'On Error Resume Next
        str1 = Cells(i, 2).Value
        If dict.Exists(str1) Then 'if the key exists
            Range("Y" & i).Value = CDate(dict.Item(str1)(1, 3))
            'add the rest of your converted code here
            '...
            '...
            '...
        End If
    Next i

    Application.ScreenUpdating = True
End Sub

I only included enough to get you started with dictionary lookups. I read each row of data into an array as the Dictionary Item and I set the Dictionary Key equal the value in the second column. 

In the demo above, the line:

Range("Y" & i).Value = CDate(dict.Item(Cells(i, 2).Value)(1, 3))

is equivalent to your vlookup formula 

Range("Y" & i).Value = CDate(Application.WorksheetFunction.VLookup(Cells(i, 2), Range("DataTable"), 3, False))

The 3 in the (1,3) portion at the end tells the code to grab the 3rd column once it finds the matching value in the 2nd column (the KEY). 

I ran some studies and it showed this approach actually wasn't any faster than the vlookup version with the data table range set to a variable, so I still think that's your best bet (and it's the easiest to implement). 

by Beginner (2 points)

Hey!

Thanks a lot for the above, really useful.

However, replacing the ranges by variables did not reduce the time significantly for me. I am working with two datasets each of roughly 1,00,000 rows.

Hence I decided to try the dictionary. However, the code suggested by you did not work for me.

Range("Y" & i).Value = CDate(dict.Item(Cells(i, 2).Value)(1, 3))

This line of the code did not work for me. It was not pasting the values appropriately.

Let me try and explain the exact situation.

Sample data:

Table 1

Employee CodeEmployee NameDate of BirthDate of JoiningSalary      DOB (Table 2)DOJ(Table 2)Salary(Table2)
1 01-04-196105-10-200815000   
2 05-10-197803-06-201525000   
3 06-08-198502-08-20189500   
4 19-05-199520-02-201580000   
5 08-06-198212-05-201060000   


Table 2:

 

Employee CodeEmployee NameDate of BirthDate of JoiningSalary      
1 01-04-196105-10-200810000
2 05-10-197803-06-201522000
3 06-08-198502-08-20188000

Both these tables are present in the same excel sheet across different columns.

What I want to do is run a loop through table 1, look for the employee code in table 2 and paste the corresponding Date of birth, date of joining and salary from table 2 in table 1. 

by Super Expert (2.4k points)

I set up the dictionary such that it stores the values in column 2 of the tables as the "Keys." It looks like youre Table 1 and Table 2 examples will require column 1 be the key. You'll need to change all the instances of the Cells(i, 2) to Cells(i, 1) for it to use the 1st column as the key. 

With that said, I don't think it's going to speed up your macro too much. It sounds like you've optimized the lookup routine as best you can.

Truthfully, it sounds like what you're trying to do on the scale you're trying to do it on would be better handled outside VBA; perhaps with a SQL query using the JOIN operator. You can do this from Excel with Microsoft Query (Data > From Other Sources > From Microsoft Query) You might have to save your tables as separate workbooks to do that. You can also do this with Power Query in Excel.

My only other suggestion is to read your entire dataset into arrays and using the Array Method described in my first comment. You'll have nested loops that abort when you find a match, but with multiple 100k datasets, it's not going to be fast

0 votes
by Beginner (4 points)

Hello community,

I'm Christophe, and a newone on this Wellsr site.

Regarding this post, i just send a link from an other site (recently found but not tested yet), about method to accelerate VLOOKUP fonction, mainly for large data sets.

Sorry, it's in french...

https://www.excel-exercice.com/accelerer-la-fonction-recherchev/

Just hope to help ...

Bye

 

 

by Beginner (2 points)
Hi Christophe,

Thanks for the suggestion. However, I require exact values. Hence I cannot make the last argument of VLookUp as TRUE.
by Beginner (4 points)

Hi,

of course you need right value, the method shows how to accelerate VLOOKUP, and get right value.

Trick is to do the research only when first criteria is true; then VLOOKUP is manage as a binary one, and no more as a linear one.

In video, the example shows same treatment about 50.000 lines: 10 minutes becomes 5 seconds !

=IF(VLOOKUP(A2;$A$2:$H$50000;1;1)=A2,VLOOKUP(A2;$A$2:$H$50000;4;1), "Unknown")

Just column A must be sorted.

Bye

 

 

 

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...