Comparing Excel sheets with only a few overlapping records

[Closed]
Report
Posts
1
Registration date
Monday January 26, 2015
Status
Member
Last seen
January 26, 2015
-
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
-
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.

1 reply

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
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