Finding duplicates in two worksheets

[tf37]

Finding duplicates in two worksheets
Rows A – H in both worksheets 1 & 2 all contain the same information
Sheet 1 has rows 1 – 14,700 records
Sheet 2 has rows 1 – 7001 records, all of which are included in sheet 1
Wish to find the duplicates in sheet 2 that are in sheet 1 and highlight those rows in sheet 1 (A-H preferred, but highlight the entire row ok too) yellow in

Then later able to sort those with yellow to bottom or top of the sort

Row headers are:
FirstName, MiddleInitial, LastName, Sex, DOB, HomePhone, Employment, WkPhone

Thank you gang. Years ago there was a similar post, but I can't seem to find any of my posts from years ago.
Terry

 

[Fluff]

Re: Help in finding duplicates in two worksheets

How about this for the 1st part

Sub FindDupes()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.value2
   With CreateObject("scripting.dictionary")
      For i = 2 To UBound(Ary)
         .Item(Join(Application.Index(Ary, i, 0), "|")) = Empty
      Next i
      Ary = Sheets("Sheet1").Range("A1").CurrentRegion.value2
      For i = 2 To UBound(Ary)
         If .exists(Join(Application.Index(Ary, i, 0), "|")) Then Sheets("Sheet1").Range("A" & i).Resize(, 8).Interior.Color = vbYellow
      Next i
   End With
End Sub

 

[tf37]

Re: Help in finding duplicates in two worksheets

Thank you, I'll give it a try

 

[tf37]

Re: Help in finding duplicates in two worksheets

That did work out just fine, thank you for the help

 

[Fluff]

Re: Help in finding duplicates in two worksheets

Glad to help & thanks for the feedback