Code To Find Duplicates And Highlight

[Dazzawm]

Is there a code or formula for when there is matching cells in column C that will then look in column AE and highlight any duplicate values please?

 

[Dazzawm]

Have you tried it on your actual data? How long did it take?
Try this one to pop up a message when there's no duplicate:

Sub Dazzawm_2()
'' section group looping
Dim i As Long, j As Long, n As Long
Dim c As Range, a
Dim t As Double
t = Timer - t
Range("C:C").Interior.Color = xlNone
n = Range("C" & Rows.Count).End(xlUp).Row

For i = 2 To n
    If Len(Cells(i, "C")) > 0 Then
        j = WorksheetFunction.CountIf(Range("C" & i & ":C" & n), Cells(i, "C")) 'case insensitive
'            Debug.Print Cells(i, "A").Resize(j).Address
            a = WorksheetFunction.Unique(Cells(i, "AE").Resize(j))
            If UBound(a) < j Then
                If c Is Nothing Then
                    Set c = Cells(i, "C").Resize(j)
                Else
                    Set c = Union(c, Cells(i, "C").Resize(j))
                End If
            End If
            i = i + j - 1
    End If
Next

If Not c Is Nothing Then
    c.EntireRow.Interior.Color = vbYellow
Else
    MsgBox "No duplicates found"
End If
Debug.Print "It's done in:  " & Format(Timer - t, "0.00") & " seconds"
End Sub

BTW the timer didn't pop up

 

[Dazzawm]

Have you tried it on your actual data? How long did it take?
Try this one to pop up a message when there's no duplicate:

Sub Dazzawm_2()
'' section group looping
Dim i As Long, j As Long, n As Long
Dim c As Range, a
Dim t As Double
t = Timer - t
Range("C:C").Interior.Color = xlNone
n = Range("C" & Rows.Count).End(xlUp).Row

For i = 2 To n
    If Len(Cells(i, "C")) > 0 Then
        j = WorksheetFunction.CountIf(Range("C" & i & ":C" & n), Cells(i, "C")) 'case insensitive
'            Debug.Print Cells(i, "A").Resize(j).Address
            a = WorksheetFunction.Unique(Cells(i, "AE").Resize(j))
            If UBound(a) < j Then
                If c Is Nothing Then
                    Set c = Cells(i, "C").Resize(j)
                Else
                    Set c = Union(c, Cells(i, "C").Resize(j))
                End If
            End If
            i = i + j - 1
    End If
Next

If Not c Is Nothing Then
    c.EntireRow.Interior.Color = vbYellow
Else
    MsgBox "No duplicates found"
End If
Debug.Print "It's done in:  " & Format(Timer - t, "0.00") & " seconds"
End Sub

Sorry to be a pain but could it exclude some values in AE? I would like NDA, NLA, NA and NYA excluded when looking in AE please. But it does show me the code works great. Sorry I didn't think of this when posting.

 

[Legacy 143009]

Still the same error

The only thing that I can think of is your Scripting Library is not enabled. Please follow the steps below:

https://www.wallstreetmojo.com/vba-dictionary/

 

[Akuini]

BTW the timer didn't pop up

Debug.Print return the result in the Immediate Window (in vba editor).

Sorry to be a pain but could it exclude some values in AE? I would like NDA, NLA, NA and NYA excluded when looking in AE.

I'll try.

 

[Dazzawm]

The only thing that I can think of is your Scripting Library is not enabled. Please follow the steps below:

https://www.wallstreetmojo.com/vba-dictionary/

It is as I have it in other codes. But thanks very much for your efforts. @Akuini seems to have nailed it.

 

[Legacy 143009]

It is as I have it in other codes. But thanks very much for your efforts. @Akuini seems to have nailed it.

Very strange indeed... I am sure I've nailed it also. The code handles 30000 lines in a blink of an eye.

Just for my curiosity, when you run it in the sample file do you get the error?
At which line? What does error states?

 

[Akuini]

I would like NDA, NLA, NA and NYA excluded when looking in AE please.

Wait, is it a whole or partial match?

 

[Dazzawm]

Very strange indeed... I am sure I've nailed it also. The code handles 30000 lines in a blink of an eye.

Just for my curiosity, when you run it in the sample file do you get the error?
At which line? What does error states?

Sample file says deleted. It didn't give a line just errored straight away.

 

[Dazzawm]

Wait, is it a whole or partial match?

They will all say as I put.

 

[Legacy 143009]

Somebody must have downloaded it before you. Here find wetransfer link:

sample.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl