Previous Topic Next Topic

Comparing two excel sheets

Closed
ExcelNewbie192 Posts 1 Registration date Sunday June 7, 2015 Status Member Last seen June 7, 2015 - Jun 7, 2015 at 08:55 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 9, 2015 at 11:58 AM
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!


Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 9, 2015 at 11:58 AM
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

0

Similar discussions

Comparing Two Excel Sheets and copying like data to third sheet
coz2992 -
TrowaD -

11 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses