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

Vinothkumar

New Member
Joined
Oct 17, 2015
Messages
7
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:
IDNameSTATUS
101RamINACTIVE
102JohnACTIVE
103SteveACTIVE
104AaronACTIVE
105RamACTIVE
106JohnINACTIVE

<tbody>
</tbody>

<tbody>
</tbody>
Sheet2Expected Result
NameSTATUSSheet1-IDMatch?
JohnACTIVE102TRUE
SteveACTIVE103TRUE
AaronACTIVE104TRUE
RamINACTIVE101TRUE
JohnINACTIVE106TRUE
SteveINACTIVEFALSEFALSE

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Hi,

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

Code:
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

New Member
Joined
Oct 17, 2015
Messages
7
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
 

Forum statistics

Threads
1,147,482
Messages
5,741,402
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top