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

 

[Mr Denove]

Hi Jack, a dry sense of humour i see lol.

That kind of code is new to me, is there any way you can explain what its doing please?

 

[JackDanIce]

More a League of Gentleman humour than dry, though partial to Jack Dee and Daniels.

There's a difference between the data the cells contain and the information around it, e.g. what cell contains the data, what sheet is it on.

You could name your sheets "Daily PnL CDS Curve Bumps" but stating that on a public discussion board has no meaning without the data to compliment it or you could lie and say "It's Sheet1 and the formula first goes into D2" either way you haven't broken any company data confidentiality rules

Code with comments:

Sub AVeryConfidentialMacro()
    
    'Variables
    Dim arr()   As Variant
    Dim dic     As Object
    Dim x       As Long
    
    'Set a variable to be a dictionary object
    Set dic = CreateObject("Scripting.Dictionary")
        
    With Sheets("Sheet1")
        'Find last row with data on column A, sheet1
        x = .Cells(.Rows.count, 1).End(xlUp).row
        'Read data from A2 to last row (x as above) in column C into an array
        'The array size must equal the data range size
        arr = .Cells(1, 2).Resize(x - 1, 3).Value
    End With
    
    'Iterate over the array
    For x = LBound(arr, 1) To UBound(arr, 1)
        'Map the key (columm A) to it's item (column C)
        dic(arr(x, 1)) = arr(x, 3)
    Next x
    'Clear the array for reuse, all key-item maps are in the dictionary
    Erase arr
    
    With Sheets("Sheet2")
        'Find last row with data on column A, sheet2
        x = .Cells(.Rows.count, 1).End(xlUp).row
        'Read the lookup values in column A, into an array
        'The array size must equal the data range size
        arr = .Cells(2, 1).Resize(x - 1).Value
        
        'Iterate over the array and pass the value into the dictionary and if it maps to a key
        'over-write the value back into the array, the array size matches the output size
        'So no need for separate arrays
        For x = LBound(arr, 1) To UBound(arr, 1)
            arr(x, 1) = dic(arr(x, 1))
        Next x
        'Print out results
        .Cells(4, 2).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    End With
    
    'Clear variables
    Erase arr
    Set dic = Nothing


End Sub

A dictionary allows random access between a key and item, once those pairs of data has been read in.

 

[JackDanIce]

Sorry mistake in code and then browser froze so couldn't edit.

Change the line below the comment to:

'The array size must equal the data range size
        arr = .Cells(2, 1).Resize(x - 1, 3).Value

I wrongly posted it in above as:

arr = .Cells(1, 2).Resize(x - 1, 3).Value

 

[Mr Denove]

Thank you but sorry few more questions so I can understand this code a bit more. 'Iterate over the array no idea what that means.

Is this doing something similar in that its comparing two references but storing them in the Dictionary for reference.

How do I put output into another column if I need to?

Again thanks in advance.

 

[JackDanIce]

Iterate or loop over an array

I use an array to read data from Sheet1 because I can do that in a single line of code and it's faster to iterate (loop) over an array in memory, that cells on a worksheet.

I use the dictionary to map the key value (column A in sheet 1 is the first index in the array, though with a dictionary, you can have your key in any column and item in an other column, order doesn't matter) to it's item value (column C, sheet1 which is the 3rd column in the array)

With sheet 2, I write all the values you want to "VLOOKUP" into an array.

I then loop over the array and pass every key/lookup value through the dictionary and if it exists, I replace the key/lookup value with it's mapped value (trying to be efficient, there isn't a need for a separate output array)

My array is now the same size as all the lookup values, but replaced with the item or mapped values

I write it out to column D, the range size is the same size as the array.

Processing data in memory removes need to update screen until final output is ready to be printed to the sheet, so no need to turn it off in this instance.

Since a dictionary has random access, it can point to any key/item pair within it, there is no difference pointing to the first pair or the nth pair.

You can change the values under 'Print out results where:
Cells(r, c) is the start cell with row r, column number c

So D2 is cells(2, 4), F8 is cells(8, 6), J10 is cells(10, 10), etc

 

[steve the fish]

Theres a chance this would be quicker if you did want formula:

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

 

[Mr Denove]

Not quite sure where I am going wrong, Ive amended the fix as you advised thanks, but Im not getting any results even tho the code is compiling.

 

[JackDanIce]

Not quite sure where I am going wrong, Ive amended the fix as you advised thanks, but Im not getting any results even tho the code is compiling.

How have you amended the fix? What was the need for the fix? You stated formulas go into

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

Is this another case of ignore precision, you can see your worksheet but if you provide generic information for readers there's a chance they'll backout what you actually want?

 

[Mr Denove]

Sorry mistake in code and then browser froze so couldn't edit.

Change the line below the comment to:

'The array size must equal the data range size
        arr = .Cells(2, 1).Resize(x - 1, 3).Value

I wrongly posted it in above as:

arr = .Cells(1, 2).Resize(x - 1, 3).Value

you provided the above amendment.

I have no output in column D.

The range of column D is dynamic depending on the number of records produced each day.

 

[Aladin Akyurek]

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

What is the VLOOKUP formula that you implement in Sheet2? Example:

=VLOOKUP(A2,B2:C400,2,0)