Previous Topic Next Topic

Comparing two excel sheets

[Closed]
Report
Posts
1
Registration date
Sunday June 7, 2015
Status
Member
Last seen
June 7, 2015
 -
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 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

Answer 1 / 1
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
 486
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

Ask a question