Need vba macro to highlight duplicates in a column

[estatefinds]

Hello, I need a macro that i can run that will highlight duplicates in a column, specifically in column W.
Thank you!

[Bob Phillips]

Conditional formatting with a formula of

=COUNTIF(A:A,A1)>1

is all you need, no VBA required

[estatefinds]

If at all possible with what Im working with I would like a VBA macro please?
Thank you

[estatefinds]

reason for a macro is that with data I have it takes way too long with formulas.

[PAB]

Hi Dennis,

Give this a go...


Sub Highlight_Duplicates_In_Column_W()
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
lastRow = Cells(Rows.Count, "W").End(xlUp).Row
For iCntr = 1 To lastRow
If Cells(iCntr, 23) <> "" Then
matchFoundIndex = _
WorksheetFunction.Match(Cells(iCntr, 23), Range("W1:W" & lastRow), 0)
If iCntr <> matchFoundIndex Then
Cells(iCntr, 23).Interior.Color = vbYellow
End If
End If
Next
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

BTW, I don't think xld's suggestion of conditional formatting with a formula will have a time impact.

I hope this helps!

[estatefinds]

ok im running it but it seems to be highlighting duplicates of the partial data what i mean is the some data in the begining will be the same but the rest is different and is still highlighting it. so each data should only high light duplicate if there is another exact match in the column, for example if there is an A6-B6-C1-B11-D12 and there is another one of the exact match of the A6-B6-C1-B11-D12 then it would highlight the original and the exact matching data. Thank you
so the data I have looks like this for Example:







A6-B6-C1-B11-D21


A6-B6-C1-A5-B5


A6-B6-C1-B12-C4


A6-B6-C16-B10-C13


A6-B6-C16-B4-D21


A6-B6-C16-B11-B5


A6-B6-C16-A5-C4


A6-B6-C16-B12-C2


A6-B6-B10-B4-B5


A6-B6-B10-B11-C4


A6-B6-B10-A5-C2


A6-B6-B4-B11-C2


A6-B6-B4-A5-B12

[PAB]

Hello, I need a macro that i can run that will highlight duplicates in a column, specifically in column W.

The question you initially asked in post #1 doesn't in any way reflect what you have now specified?

[estatefinds]

I figured since it will highlight duplicates meant that it would highlight exact matches in the column not partial, sorry I wasnt clear i just assumed it would do this.
can this be adjusted to work as I described in post 6. please
Thank you

[PAB]

I have copied your data in post #6 into a SpreadSheet and then replicated some of the data in different cells in column W and run the code.
It DOES highlight the duplicates!

[estatefinds]

i went through my data and when I did a search it only found one of them, not the duplicate.
its highlighting because the some of the data matches first part of other data. not the entire data. so its needs to highlight exact duplicates. so for example a piece of data say A1 will be in multiple data but the in the beging of the combo so it would high light, that is not what i need I need it to highlight the entire matching data so each data has 5 alpha numerics that make up the combination. so essentially it is looking for another combinations that exactly matches and there for should only be highlighted.
thank you

[PAB]

OK Dennis,

Put this data into column W in the WorkSheet and run the code.


A6-B6-B10-A5-C2
A6-B6-B10-B11-C4
A6-B6-B10-B11-C4
A6-B6-B10-B4-B5
A6-B6-B10-B4-B5
A6-B6-B4-A5-B12
A6-B6-B4-B11-C2
A6-B6-B4-B11-C2
A6-B6-C16-A5-C4
A6-B6-C16-A5-C4
A6-B6-C16-A5-C4
A6-B6-C16-B10-C13
A6-B6-C16-B11-B5
A6-B6-C16-B11-B5
A6-B6-C16-B12-C2
A6-B6-C16-B12-C2
A6-B6-C16-B4-D21
A6-B6-C1-A5-B5
A6-B6-C1-B11-D21
A6-B6-C1-B11-D21
A6-B6-C1-B11-D21
A6-B6-C1-B12-C4
A6-B6-C1-B12-C4
A6-B6-C1-B12-C4

Please let me know what is wrong, thanks.

[Bob Phillips]

I looked at PAB's code, and I couldn't see how it would highlight partial duplicates, it uses an exact match, but I ran it just to check. I used your data, and added a duplicate of the first line at the end, and another line the same as the last line with the last digit changed - a partial if ever there was one. It only found the one duplicate, just as you asked for.

[PAB]

Thanks xld,

I obviously did the same testing as yourself, mainly because I thought there might have been an error in the matching part of the code, just for my own piece of mind really.

I am wondering if there could be extra spaces in some of his cells at the beginning or the end or such like that may be throwing the code out?

[estatefinds]

ok i ran it and as youll see that the only part of the combination matches. I need ot to look highlight only complete matches. Thank you

[estatefinds]

ok i looked at it again it seems to be doing what it supposesd to. correction see if you can see why it is doinf this on my file ill send in one second. thank you

[PAB]

Hi Dennis,

I have just downloaded your file and looked at it.
The reason it appears to have ONLY highlighted partial combinations is because your data flows into the next column.
You need to widen column W, i.e. AutoFit column W.

I hope this helps!

[PAB]

Hi Dennis,

Run this amended code that will automatically AutoFit column W.


Sub Highlight_Duplicates_In_Column_W()
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
lastRow = Cells(Rows.Count, "W").End(xlUp).Row
For iCntr = 1 To lastRow
If Cells(iCntr, 23) <> "" Then
matchFoundIndex = _
WorksheetFunction.Match(Cells(iCntr, 23), Range("W1:W" & lastRow), 0)
If iCntr <> matchFoundIndex Then
Cells(iCntr, 23).Interior.Color = vbYellow
End If
End If
Next
Columns("W").EntireColumn.AutoFit
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Please let me know if it is OK.

[estatefinds]

ok it looks like its working!!! Ill let you know when it runs through all the way thank you!!!

[estatefinds]

ok so I ran it again all the way through then i took one of the highlighted data and opened up the find select function to search if there was in fact a duplicate for the highlighted number and only one was found, so this is where I ask why is it highlighted if there is only one unique combination found in the column, and not a duplicate?
I tried to send file but it was too large.
if you re run the example you asked me to run with data youll see that it highlights all and not all have duplicate data. for example the

A6-B6-C16-B4-D21 is highlighted but when i do a find search it only sees this one and no other.


disregard above it works, for some reason when i was doing the find select it wasnt picking up the second or duplicate. so I tried something else and it shows it does in fact work!!! Thank you very much!!!

[PAB]

That's great Dennis.

Don't forget to use the amended code in post #17 which AutoFits column W.

Enjoy the rest of your weekend.