I have a matrix which includes different/same values in first column, and different values in first row.

I would like to compare all rows and highlight duplicate rows. For each row, it should check the combination of “+”, “-“ and “/” values and it should highlight the duplicate row pairs(triples etc…) in different colors.(different color for each duplicate pair)

It should also assume that three rows like below are duplicates.  It will accept “/” values as “+” & “-“ and it will highlight these rows also as duplicates.

Here is an example of the result of macro that I would like to have (rows in same color are duplicates) ; enter image description here

EDIT : x4 & x7 are also duplicates with x1 & x2.And there are other duplicates which I did not colorized. I just colorized some of the duplicates in order to explain my problem.

  • What? "It will accept “/” values as “+” & “-” ..." -- I don't see anything other than “/”, “+”, and “-”, so why aren't all the rows considered the same? And "row3 is a duplicate of row3"? What does your little text block mean? Does the red text signify anything? – G-Man Dec 26 '14 at 19:14
  • Edited the text box...I made red texts in order to underline that for those cells, the "/" value behaves like "+" or "–" – NT. Dec 28 '14 at 7:14
  • 2
    I don't understand your system. For example x3 x9 appear to be the same, as do x4 x7 x8 x10 yet there does not appear to be marking setting them off. – Ron Rosenfeld Jan 8 '15 at 4:25
  • yes you're right, I just gave some examples. I did not mention all table... – NT. Jan 8 '15 at 16:11

I would restate your matching rules as follows (hopefully I am correct):

  • + matches anything in the class [+/]
  • - matches anything in the class [-/]
  • / matches anything in the class [-+/]

Given that, it is a matter of creating a pattern from the string concatenation that will acta as a matching pattern. This can be done using regular expressions, but VBA has a Like method which will work just as well, perhaps faster.

We set things up by first Inserting a Class module and rename it cRowString

Class Module

Option Explicit
Private pRow As Long
Private pColA As String
Private pConcatString As String
Private pPattern As String

Public Property Get Row() As Long
    Row = pRow
End Property
Public Property Let Row(Value As Long)
    pRow = Value
End Property

Public Property Get ColA() As String
    ColA = pColA
End Property
Public Property Let ColA(Value As String)
    pColA = Value
End Property

Public Property Get ConcatString() As String
    ConcatString = pConcatString
End Property
Public Property Let ConcatString(Value As String)
    pConcatString = Value
End Property

Public Property Get Pattern() As String
    Pattern = pPattern
End Property
Public Property Let Pattern(Value As String)
    pPattern = Value
End Property

Next enter this Regular Module

Option Explicit
Sub HilightDuplicateRows()
    Dim vData As Variant, lColors() As Long, V As Variant
    Dim colDups As Collection
    Dim R As Range
    Dim cR As cRowString, colRows As Collection
    Dim arrColors
    Dim S1 As String, S2 As String
    Dim I As Long, J As Long, K1 As Long, K2 As Long, L As Long

arrColors = VBA.Array(vbRed, vbCyan, vbYellow, vbGreen)

'get original range and load data into array
Set R = Range("a1", Cells(Rows.Count, "A").End(xlUp))
I = Cells(1, Columns.Count).End(xlToLeft).Column
Set R = R.Resize(columnsize:=I)

vData = R

'Iterate through and create patterns, collect them
Set colRows = New Collection
For I = 2 To UBound(vData, 1)
    S1 = ""
    S2 = ""
    For J = 2 To UBound(vData, 2)
        S1 = S1 & vData(I, J)
        Select Case vData(I, J)
            Case "+"
                S2 = S2 & "[+/]"
            Case "-"
                S2 = S2 & "[-/]"
            Case "/"
                S2 = S2 & "[-+/]"
        End Select
    Next J
    Set cR = New cRowString
    With cR
        .Row = I
        .ColA = vData(I, 1)
        .ConcatString = S1
        .Pattern = S2
    End With
    colRows.Add cR
Next I

'Check for duplicate pairs
Set colDups = New Collection
For I = 1 To colRows.Count - 1
    For J = I + 1 To colRows.Count
        If colRows(I).ConcatString Like colRows(J).Pattern Then
            colDups.Add CStr(colRows(I).Row & "," & colRows(J).Row)
        End If
    Next J
Next I

'Color the rows
ReDim lColors(1 To UBound(vData, 1))
J = 0
For I = 1 To colDups.Count
    V = Split(colDups(I), ",")
    If IsArray(V) Then
        Select Case lColors(V(0))
            Case 0
                J = J + 1
                K1 = J Mod (UBound(arrColors) + 1)
                lColors(V(0)) = arrColors(K1)
                lColors(V(1)) = arrColors(K1)
            Case Else
                lColors(V(1)) = lColors(V(0))
        End Select
        lColors(V) = xlAutomatic
    End If
Next I

R.Interior.Color = xlAutomatic
For I = 1 To R.Rows.Count
If lColors(I) = 0 Then
    R.Rows(I).Interior.Color = xlAutomatic
    R.Rows(I).Interior.Color = lColors(I)
End If
Next I

End Sub

Select the active sheet and run the macro

  • 1st of all, thank you for the code. First- I've inserted a class module and pasted the first part of the code inside. Then- I've inserted a (regular) module and pasted 2nd part of the code. Finally, I ran the macro by selecting the active sheet with the data to be checked. I gives me following warning (imageshack.com/a/img913/3478/U6F1pV.jpg) and opens debugger. Do I make sth. wrong? Here is screenshot of the the code debugging screen :imageshack.com/a/img661/6750/58Eka6.jpg Here is the sample data that I run the macro : imageshack.com/a/img631/4632/IPStdS.jpg – NT. Jan 8 '15 at 19:20
  • You did not rename the class module as instructed. You seem to have duplicated the class module code in the regular module. – Ron Rosenfeld Jan 8 '15 at 19:42
  • Works like a charm after renaming the class module.Big thanks for the code... – NT. Jan 8 '15 at 20:07
  • @NT. You're welcome. And you can easily modify it to add more colors, if necessary. You'd need to add colors to arrColors, and also change the line K1 = J Mod 4 to something like K1 = J Mod (Ubound(arrColors) + 1) – Ron Rosenfeld Jan 9 '15 at 11:45
  • Which part do I need to edit for new colors? @Ron Rosenfeld – NT. Jan 9 '15 at 11:45

Perhaps concatenate the content (col-F), count the matches (col-G), then apply a conditional format based on the count quantity.

This approach means two row sets having the same count qty will have the same color.

XL count concatenate

XL conditional formatting

  • this method highlights only the rows which are exactly same. It does not take into account "/" should be assumed as "+" or "-" during duplicate detecting. So it's does not solve my problem.Moreover, as it's done by conditional formatting not by VBA macro, it's not applicable for me. – NT. Dec 30 '14 at 9:56
  • +1 as this is a creative way of doing what appeared to be what he was looking for – James Mertz Jan 8 '15 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.