CODEWITHSUNDEEP
vba
Dokat
Dokat

Reputation: 21

VBA code to use rather than vlookup

I have two very large tables. One of them has 12 columns and 280K rows. The other ones has 12k rowns and 33 columns. I am using vlookup to look for matching values in large table to small one. Vlookups take forever to calculate. Is there an easy way to do this with a VBA code? Can someone share a sample code for me to replicate?

Thanks

Upvotes: 0

Views: 2742

Answers (1)

Logan Reed
Logan Reed

Reputation: 902

You can use Collection object to quickly find matches. This will work very fast (if not faster than VLOOKUP) because when you add key parameter to Collection - it hashes / indexes its value with the specific goal of fast lookup later).

Moreover, for the large number of records you populate Collection once and keep reusing it, while VLOOKUP does search the entire target range repeatedly (which is way less efficient, although built-in formulas run in parallel on multiple cores plus Microsoft definitely built-in some caching for increased efficiency for repeated searches). Even then a single-threaded VBA collections should still be faster.

See example below with more information in the in-line comments.

"Big Table" is on Sheet1:

Big Table

"Small Table" is on Sheet2:

Small Table

And the code that matches records in small table to those in the big one:

Option Explicit

Sub matchRows()

    ' this is where the big table is
    Dim w1 As Worksheet
    Set w1 = Worksheets("Sheet1")

    ' this is where the small table is
    Dim w2 As Worksheet
    Set w2 = Worksheets("Sheet2")

    Dim c As New Collection ' list of match keys in big table 1
    Dim r As Range

    ' assume the match key is in col1 in both tables
    ' enumerate the keys in the big table
    For Each r In w1.Range(w1.[a2], w1.[a2].End(xlDown))
        c.Add r, r ' this stores the range (first param) and
                    ' its key (second param - taken as string
                    ' (value of the range), must be unique)
    Next r

    ' now lets try to match / vlookup records in small table against
    ' big table
    For Each r In w2.Range(w2.[a2], w2.[a2].End(xlDown))
        If contains(c, CStr(r)) Then
            ' you didn't say what you want to do after a match, so
            ' I'll just display matched key value and row number in debug console
            Debug.Print "Found match """ & r & """ at row number " & r.Row
        Else
            Debug.Print "No match found for """ & r & """ at row number " & r.Row
        End If
    Next r

End Sub

Function contains(col As Collection, key As String) As Boolean
    On Error Resume Next
    col.Item key
    contains = (Err.Number = 0)
    On Error GoTo 0
End Function

Result in Immediate Window:

Found match "data51" at row number 2
Found match "data61" at row number 3
No match found for "data81" at row number 4
Found match "data91" at row number 5

Upvotes: 2

Related Questions