Vlookup for 400k records - vba faster?

[NemanjaAndic]

Hi people! I am trying to make vlookup faster, but it isn't going as expected.
The problem is maybe that there are 400k records in base sheet and 20k in final sheet. Is there any faster way, maybe not vlookup, maybe not even vba, to get this done faster? Thanks in advance.

Here is my code:

 Sub Vlookup400k()

 Dim lr As Long

 Application.ScreenUpdating = False
 Calculation = xlCalculationManual
 DisplayPageBreaks = False
 EnableEvents = False

 lr = Range("A" & Rows.Count).End(xlUp).Row
 Range("C2:C" & lr).FormulaR1C1 = "=VLOOKUP(RC[-1],DB!C[-2]:C[-1],2,0)"

 Range("C2:C" & lr).Select
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False

 Application.ScreenUpdating = True
 Calculation = xlCalculationAutomatic
 EnableEvents = True

 End Sub

 

[RoryA]

If you sort the data on DB sheet first, you can use the binary search version of VLOOKUP which will be much faster.

Sub Vlookup400k()

    Dim lr                    As Long

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    ActiveSheet.DisplayPageBreaks = False

    ' sort DB sheet on col A
    With Sheets("DB")
        .Range("A1").CurrentRegion.Sort key1:=.Range("A1"), order1:=xlAscending
    End With

    lr = Range("A" & Rows.Count).End(xlUp).Row
    With Range("C2:C" & lr)
        .FormulaR1C1 = "=IF(VLOOKUP(RC[-1],DB!C1,1,TRUE)=RC[-1],VLOOKUP(RC[-1],DB!C1:C2,2,TRUE),"""")"
        .Value2 = .Value2
    End With
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

 

[NemanjaAndic]

Thank you Rory, it works perfect!
What is the point of binary vlookup, how it works? It is superb fast!

 

[RoryA]

Because the data is sorted, VLOOKUP can search by splitting the data in half, testing your lookup value against the last value in the first half and the first value in the second half. If it's greater than both, the first half is discarded and the process repeats (similarly, if it's less than both values, you can discard the second half). This carries on until a match is found or the lookup value falls between the two end values.
Because the binary version will return an approximate match, you need to actually use two vlookups to ensure the match is actually the one you want.

Have a read of Colin's blog here for more information: https://colinlegg.wordpress.com/2012/03/25/binary-searches-with-vlookup/

 

[NemanjaAndic]

Once again, thank you for your explanation and for the provided link. It was above all, very very useful!!! Cheers