VBA Vlookup with Scripting Dictionary

Question

I have 2 Worksheets, the first one with 40k+ (Sheet1S) entries which I have to vlookup in the second sheet (Sheet2S), which has 300k+ entries.

I have written this script to run a vlookup based on scripting dictonary which works if i adjust the for loop to 10 rows (I uncommented the actual for loop). However, it takes ages if I use all the rows:

Sub aTest()
    Dim a As Variant, i As Long
    With CreateObject("Scripting.Dictionary")
        a = Sheets("Sheet2S").Range("A1").CurrentRegion.Value
        For i = 2 To 10
       'For i = 2 To UBound(a, 1)
       .Item(a(i, 1)) = Application.Index(a, i, 0)
    Next

    a = Sheets("Sheet1S").Range("A1").CurrentRegion.Value
   'For i = 2 To UBound(a, 1)
    For i = 2 To 10
        If .exists(a(i, 1)) Then
           a(i, 2) = .Item(a(i, 1))(2)
           'a(i, 4) = .Item(a(i, 2))(3)
        Else
           a(i, 2) = "#N/A"
        End If
    Next i
    End With
    Sheets("Sheet1S").Range("a1").CurrentRegion.Value = a
End Sub

Now according to an old thread (How to optimize vlookup for high search count ? (alternatives to VLOOKUP)) the dictionary method should only be a few seconds. If I use Application.Vlookup it takes me up to 10min for the exact same sheets which is too long for me. I am using Excel 2016 and I've added the the Microsoft Scripting Runtime. Am I doing something wrong?

Thanks in advance.

Best

Your method of implementing this seems unusual to me. How many columns? And do you have any duplicates in either list to deal with? — Ron Rosenfeld, Jul 14 '17 at 14:38
Both sheets contained 15 columns and there might are duplicates. However with the paul bica's implementation it works like a charm. — Uwewewe, Jul 14 '17 at 21:10

score 0 · Answer 1 · answered Jul 14 '17 at 15:06

I tested your implementation, without the Index(), and restricted to 2 columns: took 2.2 seconds:

Option Explicit

'Use Early Binding: VBA Editor -> Tools -> References -> Add Microsoft Scripting Runtime

Public Sub VLookUpTest()
    Const N = "#,###"
    Dim a As Variant, i As Long, d As Dictionary
    Dim lr1 As Long, lr2 As Long, t As Double, tt As String

    t = Timer
    Set d = New Dictionary
    With d

        a = Sheets("Sheet2S").Range("A1").CurrentRegion.Columns("A:B").Formula
        lr2 = UBound(a)
        For i = 2 To lr2
            .Item(a(i, 1)) = a(i, 2)
        Next

        a = Sheets("Sheet1S").Range("A1").CurrentRegion.Columns("A:B").Formula
        lr1 = UBound(a)
        For i = 2 To lr1
            a(i, 2) = IIf(.exists(a(i, 1)), .Item(a(i, 1)), "#N/A")
        Next i
        Sheets("Sheet1S").Range("a1").CurrentRegion.Columns("A:B").Formula = a

    End With

tt = Format(Timer - t, "#,###.00") & " seconds"
Debug.Print "S1 rows: " & Format(lr1, N) & "; S2 rows: " & Format(lr2, N) & "; Time: " & tt

    'S1 rows: 50,001; S2 rows: 350,001; Time: 2.23 seconds

End Sub

Wow this is amazing!!! Thanks alot. Just for fun, I've tested it on Sheet1 and Sheet2 with 300000x15 and it was still really fast considering the size of the sheets. 'S1 rows: 306 271; S2 rows: 306 271; Time: 267,23 seconds — Uwewewe, Jul 14 '17 at 21:04

VBA Vlookup with Scripting Dictionary

1 Answers1