Help with Scripting Library

[termeric]

I have 2 sets of data that I need to compare, each set is roughly 500,000 rows. I tired to do a vlookup but it was taking far too long to complete (30+ minutes before I cancelled it) I've been reading that I want to use a scripting library to accomplish this, but I cant quite figure out how to make it work. If I have my data in column A on sheet1 and sheet 2, what would I use to compare the two and mark in column B if they match?

 

[JackDanIce]

I'd run a macro over your data for this.

Try following (maybe on a copy of your workbook). It assumes sheet names are "Sheet1" and "Sheet2", no headers in either sheet, data starts in A1 and the last row with data is found in column A for both sheets and results to be printed to Sheet1:

Sub MarkDups()


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

 

[termeric]

I'm getting a type mismatch runtime error when I try to run it. I also tried on a new book with only a handful of rows.

 

[Fluff]

Hello Jack
Trying to understand your code & am getting a couple of errors

Sub MarkDups()


    Dim dic     As Object
    Dim x       As Long
    Dim arr1()  As Variant
    Dim arr2()  As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    With Sheets("Sheet1")
        x = .Cells(.Rows.Count, 1).End(xlUp).row
        arr1 = .Cells(1, 1).Resize(x, 2).Value
    End With
            
    With Sheets("Sheet2")
        x = .Cells(.Rows.Count, 1).End(xlUp).row
        arr2 = .Cells(1, 1).Resize(x).Value
    End With
    
    For x = LBound(arr[COLOR=#ff0000]2[/COLOR], 2) To UBound(arr[COLOR=#ff0000]2[/COLOR], 2)
        dic(arr2(x, 1)) = "Duplicate"
    Next x
    Erase arr2
    
    For x = LBound(arr1, 1) To UBound(arr1, 1)
        arr1(x, 2) = dic(arr1(x, 1))
    Next x
    Set dic = Nothing
    
    Sheets("Sheet1").Cells(1, 1).Resize(UBound(arr1, 1), UBound(arr1, 2)).Value = arr[COLOR=#ff0000]1[/COLOR]
    Erase arr2
    
End Sub

The 3 bits in red are what I've changed, could you check I've got it right?
Cheers

 

[JackDanIce]

Good spot and thanks @Fluff, @temeric, try the corrected code but change the very last Erase arr2 to Erase arr1

 

[Fluff]

@JackDanIce
Hia
Not sure I've ever seen a dictionary written like this before. Would I be correct in thinking this line

dic(arr2(x, 1)) = "Duplicate"

is the same as

dic.Add arr2(x, 1), "Duplicate"

 

[JackDanIce]

It does work, I've used it before It's effectively still Dic(Key) = Item, equality symbol I guess? The mapping works at anyrate

 

[Fluff]

Cheers for that, just never seen it done that way before