Highlight Duplicate Rows (In Series)

  • Thread starter Thread starter Rugby Stud
  • Start date Start date
R

Rugby Stud

I'm looking for a way to highlight duplicate rows that cover a series of
cells. For example, each row in A1:A5 must match each row in A55:E55 to be
considered a duplicate.

What's the best way to go about this?
 
probably add a column which concatenates the data from the used cells in a
row - depends on how many columns you have
you could then use the countif() formula to highlight where the duplicates
are ( countif > 1)
 
Hi Patrick,

Sounds promising... how do I do it? :-)
If I want to concat the data in range ?1:?5 and then use countif()?
 
Option Explicit
Sub GetDuplicates()
Dim text As String
Dim cols As Long
Dim index As Long
Dim lastrow As Long
cols = 10 ' number of columns to check
lastrow = Range("A1").End(xlDown).Row
'add a column to get unique contants for say 10 columns
Columns(1).Insert
'built the text contetns
For index = 1 To cols
text = text & "& RC" & index + 1
Next
'then place in column 1
With Range("A1")
.FormulaR1C1 = "=" & Mid(text, 2)
.AutoFill Destination:=Range(Range("A1"), Cells(lastrow, "A"))
End With

' now add another column for the counter
Columns(1).Insert
With Range("A1")
.Formula = "=COUNTIF(B1:B" & lastrow & ",B1)"
.AutoFill Destination:=Range(Range("A1"), Cells(lastrow, "A"))
End With
'clean up
With Range(Range("A1"), Cells(lastrow, "A"))
.Calculate
.Value = .Value
End With
Columns(2).Delete

End Sub
 
Thank you, Patrick! I appreciate your help.

Patrick Molloy said:
Option Explicit
Sub GetDuplicates()
Dim text As String
Dim cols As Long
Dim index As Long
Dim lastrow As Long
cols = 10 ' number of columns to check
lastrow = Range("A1").End(xlDown).Row
'add a column to get unique contants for say 10 columns
Columns(1).Insert
'built the text contetns
For index = 1 To cols
text = text & "& RC" & index + 1
Next
'then place in column 1
With Range("A1")
.FormulaR1C1 = "=" & Mid(text, 2)
.AutoFill Destination:=Range(Range("A1"), Cells(lastrow, "A"))
End With

' now add another column for the counter
Columns(1).Insert
With Range("A1")
.Formula = "=COUNTIF(B1:B" & lastrow & ",B1)"
.AutoFill Destination:=Range(Range("A1"), Cells(lastrow, "A"))
End With
'clean up
With Range(Range("A1"), Cells(lastrow, "A"))
.Calculate
.Value = .Value
End With
Columns(2).Delete

End Sub
Click to expand...
 
Back
Top