Color rows red when there are duplicated values in three columns-CodePudding

I am struggling to color rows red when there are duplicated values

I have a table like this that might have a different number of rows in xlsm file that has several sheets, so I need to look for the right worksheet. All three rows should have the same length.

orderId	OrderNumber	PositionNumber
something1	1	1
something1	1	2
something2	1	1
something2	2	1
something2	2	1
something3	3	1
something2	2	1

The rows have the same data in all three columns, so they are duplicates that should be colourd red.

orderId	OrderNumber	PositionNumber
something2	2	1
something2	2	1
something2	2	1

Now I have been trying to colour it red, but I know next to nothing about VBA and it is hard for me to figure out how to do it.

I need to also do it for two columns in other sheet, but I guess once I can do it for three I can do it for two columns as well.

I have tried to write something, but it doesn't work.

Sub lookForDuplicates()

Dim C1 As Range, Rng As Range
Dim Value As String

For Each C1 In orders.Range("A2", orders.Range("B" & orders.Range("C", Rows.Count)).End(xlUp))
    Vlu = Cl.Value & "|" & Cl.Offset(, 1).Value & "|" & Cl.Offset(, 2).Value & "|" & Cl.Offset(, 3).Value
    If Vlu.exists Then
        row.Interior.Color = vbRed
    
    End If
Next C1
End Sub

CodePudding user response：

You don't need VBA, you can just use Conditional Formatting with a Formula.

Imagine for a moment that these 3 Columns are A, B, and C, and you want to add a new Column, D, which says True or False, depending on whether the row is a duplicate.

If Row 2 is a duplicate, then there will be more than 1 Row where Column A contains the value from Cell A2, Column B contains the value from Cell B2, and Column C contains the value from Cell C2.

To count how many rows match those criteria, we can use a COUNTIFS function in a Worksheet Formula:

=COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)

To convert this into True and False if more than 1 Row matches (because, this will also count Row 2 itself!), we just need to ask it ">1?"

=COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)>1

And that True/False result is exactly what you need for a Conditional Formatting rule under "Use a formula to determine which cells to format"!

If you want to only colour the repeats (i.e. the second and subsequent appearance of the rows, but not the first) then you want to only check the Column so far, and not the entire Column:

=COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2,$C$1:$C2,$C2)>1

If you want to colour the first entry in a different colour when it has repeats, then you can create another Conditional Formatting entry using an AND to contrast both versions:

=AND(COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)>1, NOT(COUNTIFS($A$1:$A2,$A2,$B$1:$B2,$B2,$C$1:$C2,$C2)>1))

CodePudding user response：

Please, try the next code. It will color in red only rows being duplicate, I mean starting from the second occurrence of the same row content. If you need deleting the duplicate rows, you can simple replace rngRed.Interior.Color = vbRed with rngRed.EntireRow.Delete:

Sub lookForDuplicates()
 Dim shO As Worksheet, lastR As Long, rng As Range, arr
 Dim rngRed As Range, i As Long, dict As New Scripting.Dictionary

 Set shO = ActiveSheet 'use here the sheet you need
 lastR = shO.Range("A" & shO.rows.count).End(xlUp).row
 Set rng = shO.Range("A2:C" & lastR)
 arr = rng.Value2 'place the range in an array for faster iteration

 For i = 1 To UBound(arr)
    If Not dict.Exists(arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3)) Then
        dict.Add arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3), vbNullString
    Else
        addRange_ rngRed, rng.rows(i)
    End If
 Next i
 If Not rngRed Is Nothing Then rngRed.Interior.Color = vbRed
End Sub
Sub addRange_(rngU As Range, rngAdd As Range)
    If rngU Is Nothing Then
        Set rngU = rngAdd
    Else
        Set rngU = Application.Union(rngU, rngAdd)
    End If
End Sub

Coloring a row at a time is much slower, that's why previously placing them in a Union range and color all the range at once, at the end, is much faster.

If the Union range can be huge (more than 2000 areas, or even more...), the Union range building will start slowing the code. In such a case I can supply a different code able to let the code running fast, even for such cases

If necessary, the above code can be easily adapted to color the initial row, too...