Comparing two excel sheets [Closed]

Report
Posts
1
Registration date
Sunday June 7, 2015
Status
Member
Last seen
June 7, 2015
-
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
-
Hello,
i have 2 excel sheets, each with 2 columns name and email. the scenario is i want to compare both sheets and display those which has the same name and email in another sheet 3. how do i go about doing this?

thanks!


1 reply

Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
454
Hi ExcelNewbie,

Try the code below but don't forget to adjust the sheet references (sheet1 on line 5 , sheet2 on line 10 and sheet3 on line 13) to match yours:

Sub RunMe()
Dim lRow As Integer
Dim fValue As Range

Sheets("Sheet1").Select

lRow = Range("A1").End(xlDown).Row

For Each cell In Range("A2:A" & lRow)
    Set fValue = Sheets("Sheet2").Columns("A:A").Find(cell.Value)
    If fValue Is Nothing Then GoTo NextCell
    If cell.Offset(0, 1).Value = fValue.Offset(0, 1).Value Then
        Range(cell, cell.Offset(0, 1)).Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
NextCell:
Next cell

End Sub


Let me know how it goes.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!