Identifying Duplicate Rows within a Range

[nachiks]

I get a series of numbers and some gets repeated.And I want to identify them by coloring the cell.

[ExcelTip]

Problem:

The range A2:C6 contains a list of items with their matching categories and prices.
Any rows containing identical entries are duplicate records.
We want to identify each duplicate record in the range.
Solution:

Use the SUMPRODUCT function as shown in the following formula:
=SUMPRODUCT((B2=$B$2:$B$6)*(A2=$A$2:$A$6)*(C2=$C$2:$C$6))>1

[sjumbo]

The macro I am using for marking duplicates in a range is:

Sub Mark_Duplicate_Rows()
'WARNING: PLEASE SORT & SELECT YOUR RANGE BEFORE EXECUTING THIS MACRO
If MsgBox("Have you sorted & selected your range?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
R1 = Selection.Cells(1).Row
K1 = Selection.Cells(1).Column
RL = Selection.Cells(Selection.Cells.Count).Row
KL = Selection.Cells(Selection.Cells.Count).Column
CC = Selection.Columns.Count
If R1 = RL Then
MsgBox "Select atleast two rows!"
Exit Sub
End If
RDupe = 0
For j = R1 To RL
Duplicate = 0
For i = K1 To KL
If j > R1 Then
If Cells(j, i).Value = Cells(j - 1, i).Value Then
Duplicate = Duplicate + 1
End If
End If
Next
If Duplicate = CC Then
Range(Cells(j, K1), Cells(j, K1 + CC - 1)).Select
Selection.Font.ColorIndex = 3
Range(Cells(j - 1, K1), Cells(j - 1, K1 + CC - 1)).Select
Selection.Font.ColorIndex = 3
RDupe = RDupe + 1
End If
Next
Application.ScreenUpdating = True
Range(Cells(R1, K1), Cells(RL, KL)).Select
'If RDupe > 0 Then MsgBox Trim(Str(RDupe)) & " Rows!"
End Sub

From:
Jumbo S.Subramanian from Salem, India

[crisjr]

This is great, how do I merge the duplicates instead?

[VBA Noob]

crisjr,

Please start your own thread and attach a link to this one.

VBA Noob