**finding duplicates from multiple columns and highlighting**



I have a list consisting of 2 columns that a duplicate may appear in 1 column
but if the second column has a different value it is still unique. I wish to
check this list against a second master list (containing all the info) and
find the duplicates in the first list and copy these duplicates to a second
sheet in the same document. Lists contain both numbers and letters. List is
over 10,000 rows so need to have no manual part to this.




Sub CheckingForDuplicates()

'i have done it for 5 rows only. Change it to 10000

'assuming your lists are in Columns A & B and starting in the first row

Cells(1, 3).FormulaR1C1 = "=rc[-2]&rc[-1]"
Cells(1, 4) = 1
Cells(2, 4).FormulaR1C1 = "=r[-1]c+1"
Cells(1, 3).Copy Range("c2:C5")
Cells(2, 4).Copy Range("d3:d5")
Range("c1:c5").Value = Range("c1:c5").Value
With Sheets(1).Sort
..SetRange Range("c1:c5")
End With

'this section lists the duplicates in sheet 2

Dim Rows As Integer
Dim SheetsTwoRows As Integer
SheetsTwoRows = 1
For Rows = 2 To 5
If Cells(Rows + 1, 3).Value = Cells(Rows, 3) Then
Sheets(2).Cells(SheetsTwoRows, 1) = Cells(Rows, 3)
Sheets(2).Cells(SheetsTwoRows, 2) = Cells(Rows, 4)
SheetsTwoRows = SheetsTwoRows + 1
End If

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question