Previous Topic Next Topic

Comparing Excel sheets with only a few overlapping records

Closed
lynn.frances Posts 1 Registration date Monday January 26, 2015 Status Member Last seen January 26, 2015 - Jan 26, 2015 at 05:40 PM
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 - Jan 26, 2015 at 08:05 PM
Hello,
I have two worksheets (1 & 2) that have different info, with not many duplicate records.
I need to identify the duplicate records.
I can do it by email address, which is row A in each sheet.

I don't want it to compare row for row, I want it to look through the entire column of Sheet 2 to see if each entry in row A of Sheet 1 is included.

Then, I want it to create Sheet 3 with all matching email addresses. Or, if it's easier, it can add TRUE FALSE in a new column on Sheet 1. How do I do this? Thanks in advance for your assistance.
Related:

1 response

Answer 1 / 1
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Jan 26, 2015 at 08:05 PM
This is based on code I wrote the other day for another question but with a few minor changes. 


Sub FindDupes()
Dim lastRow1 As Long, lastRow2 As Long

Sheet1.Select
lastRow1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
lastRow2 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Sheet2.Range("A2:A" & lastRow2)
 On Error Resume Next
 r = Rows(Application.Match(cell.Value, Sheet1.Range("A2:A" & lastRow1), 0)).Row
 If Err.Number = 0 Then
    cell.Copy Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
 End If
Next
Sheet3.Select

End Sub
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