The best way to compare 50,000 rows of data to another of 130,000? Vlookup to slow

[Mr Denove]

Morning All,

On a daily basis I am looking to create a report that takes 50,000 circa rows of data and then compares it to another list of 130,000 rows of data. If I use a vlookup I might as well go get lunch as it takes so long to run due to the limitations of the pc memory etc.

Can anyone suggest a quick and efficient way to resolve this please?

Thanks in advance
Stuart

 

[JackDanIce]

Can you give more information:

What is the formula you use?
What are the names of the sheets?
What is the full data range of the data in those sheets?
What cell does the formula go into - assume it's for all rows in that column?

A macro may be faster, depending on where the data is static or derived from formula. If formula, you can try turning calculation mode to manual, fill in your formula, then hit F9 to calculate, and then turn calculation mode to automatic

 

[Mr Denove]

Im using a vlookup and using the small range (50,000) rows to check if they are in the larger range (130,000). I have tried a macro with screen updates off, calculation manual etc but when I re apply the calculation to automatic I then sit for 20-30 mins watching nothing due to the size of data.
Sheet1 and Sheet2 keeping it basic and in the same workbook.

 

[JackDanIce]

I can't see your screen. You're using a VLOOKUP, what is the key to look up? What is the range to match again? What is the column index number to return mapped item from? Is it an exact match?

You can do the map within VBA and output the results directly to the sheet, saving you calculation time, using a Dictionary object to create the mapping, with an array to read the data in.

Consider who can see your PC monitor and who can't. Giving precise information means not needing to ask questions like requesting the data range, what cell does the formula go into, etc, or if you've tried a macro and didn't get far, why are you asking for help but not willing to share specific information that you can see in front of you on your spreadsheet...

 

[Mr Denove]

You might appreciate that I cant provide too much detail given its work and highly sensitive data thats being used.
the formula is =vlookup(a2,Sheet1, Range in here which is dynamic,3,false)

You mention I can do the map within VBA and output the results to the sheet. Can you please explain how I would do that? Never used it hence the reason I came on here to ask as I thought there is a way possible with a macro i just didnt know what it was.

Thanks

 

[Aladin Akyurek]

Im using a vlookup and using the small range (50,000) rows to check if they are in the larger range (130,000). I have tried a macro with screen updates off, calculation manual etc but when I re apply the calculation to automatic I then sit for 20-30 mins watching nothing due to the size of data.
Sheet1 and Sheet2 keeping it basic and in the same workbook.

care to post that formula?

 

[Mr Denove]

As requested

=VLOOKUP(A2,'Sheet1'!$A$2:$C$130522,3,FALSE)

 

[JackDanIce]

You are not sharing the data. You are sharing the cell address that the formula should go into, e.g. Sheet1, cell G1 or the sheet name, how is that confidential?

What cell does that formula go into?

 

[Mr Denove]

The formula is going into Sheet2, D2:D55693 (which changes frequently).

 

[JackDanIce]

Try:

Sub AVeryConfidentialMacro()

    Dim arr()   As Variant
    Dim dic     As Object
    Dim x       As Long
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    With Sheets("Sheet1")
        x = .Cells(.Rows.count, 1).End(xlUp).row
        arr = .Cells(1, 2).Resize(x - 1, 3).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 1)) = arr(x, 3)
    Next x
    Erase arr
    
    With Sheets("Sheet2")
        x = .Cells(.Rows.count, 1).End(xlUp).row
        arr = .Cells(2, 1).Resize(x - 1).Value
        
        For x = LBound(arr, 1) To UBound(arr, 1)
            arr(x, 1) = dic(arr(x, 1))
        Next x
        
        .Cells(4, 2).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    End With
    Erase arr
    
    Set dic = Nothing

End Sub