Compare two column values in different sheet and populate Sheet1 Column A values in Sheet2

[Vinothkumar]

Hi,
I have ID(Col A),NAME(Col B) and STATUS(Col C) Column in Sheet1. In Sheet2 NAME(Col A) and STATUS(Col B) columns are existing.
My requirement is, i have to compare sheet1 column A and B with Sheet2 Column A and B. If it is match then Sheet1 Col A value should be populated in Sheet2 Col C and make Col D as 'True'.
I have written the code for it and working fine for less no. of records. Since my sheets contains millions of record, it is hanging like anything. Can you please help me on this?

Mockup Data:
Sheet1:
[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD][TABLE="width: 235"]
<tbody>[TR="class: grid"]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]STATUS[/TD]
[/TR]
[TR="class: grid"]
[TD]101[/TD]
[TD]Ram[/TD]
[TD]INACTIVE[/TD]
[/TR]
[TR="class: grid"]
[TD]102[/TD]
[TD]John[/TD]
[TD]ACTIVE[/TD]
[/TR]
[TR="class: grid"]
[TD]103[/TD]
[TD]Steve[/TD]
[TD]ACTIVE[/TD]
[/TR]
[TR="class: grid"]
[TD]104[/TD]
[TD]Aaron[/TD]
[TD]ACTIVE[/TD]
[/TR]
[TR="class: grid"]
[TD]105[/TD]
[TD]Ram[/TD]
[TD]ACTIVE[/TD]
[/TR]
[TR="class: grid"]
[TD]106[/TD]
[TD]John[/TD]
[TD]INACTIVE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD][TABLE="width: 299"]
<tbody>[TR="class: grid"]
[TD]Sheet2[/TD]
[TD][/TD]
[TD="colspan: 2"]Expected Result[/TD]
[/TR]
[TR="class: grid"]
[TD]Name[/TD]
[TD]STATUS[/TD]
[TD]Sheet1-ID[/TD]
[TD]Match?[/TD]
[/TR]
[TR="class: grid"]
[TD]John[/TD]
[TD]ACTIVE[/TD]
[TD="align: right"]102[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR="class: grid"]
[TD]Steve[/TD]
[TD]ACTIVE[/TD]
[TD="align: right"]103[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR="class: grid"]
[TD]Aaron[/TD]
[TD]ACTIVE[/TD]
[TD="align: right"]104[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR="class: grid"]
[TD]Ram[/TD]
[TD]INACTIVE[/TD]
[TD="align: right"]101[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR="class: grid"]
[TD]John[/TD]
[TD]INACTIVE[/TD]
[TD="align: right"]106[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR="class: grid"]
[TD]Steve[/TD]
[TD]INACTIVE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[RickXL]

Hi,

I assumed that you meant that columns B and C of Sheet1 needed to match A and B of Sheet2?

Sub Compare()

    Dim ws1  As Worksheet
    Dim ws2  As Worksheet
    Dim i    As Long
    Dim dic  As Object
    Dim Key  As String
    Dim arr1 As Variant
    Dim arr2 As Variant

    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set dic = CreateObject("Scripting.Dictionary")

    With ws1
        arr1 = .Range("A2").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 1, 3)
    End With

    For i = 1 To UBound(arr1)
        Key = arr1(i, 2) & "|" & arr1(i, 3)
        dic(Key) = arr1(i, 1)
    Next
    
    With ws2
        arr2 = .Range("A2").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 1, 4)
        For i = 1 To UBound(arr2)
            Key = arr2(i, 1) & "|" & arr2(i, 2)
            If dic.Exists(Key) Then
                arr2(i, 3) = dic.Items()(i)
                arr2(i, 4) = True
            Else
                arr2(i, 3) = False
                arr2(i, 4) = False
            End If
        Next
        .Range("A2").Resize(UBound(arr2, 1), UBound(arr2, 2)) = arr2
    End With

End Sub

The macro:
Reads the Sheet1 data into an array;
Creates a direct access Dictionary that has all the keys and all the IDs in it;
Reads the Sheet2 data into an array;
Steps through the array and looks for matching keys in the dictionary;
If the Key exists in the Dictionary it updates the data with ID and TRUE otherwise it sets everything to False.
Finally, it writes the completed array back to Sheet2.

 

[Vinothkumar]

Hi Rick,
Thanks a lot for your help!

When i try with above code for one lakh records i am getting "Run time error '9': Subscript Out of range" error.

Thanks,
Vinoth