**finding duplicates from multiple columns and highlighting**


K

kyle.macdonald

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.

Thanks

Kyle
 
Ad

Advertisements

V

vavasoo

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")
..Apply
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
Next

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

Top