Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 27
  • Last Modified:

VBA code that detects duplicates in a column

Hi everyone,

I have a spreadsheet where one of the columns contains a list of names (both first and last names) and I've sorted the column so that it's in alphabetical order. Some of the rows in that column contain duplicate names so I was wondering if I could get some help with some vba code that would look down the column (across all rows) and see if it finds the same name and if it does then the consecutive rows would be highlighted a yellow colour? The same names might be in row 1 to 4 or it could be in rows 7 and 8 or any other consecutive rows.

The names would be in Column G.

Would really appreciate any help.
0
gwh2
Asked:
gwh2
  • 3
  • 3
  • 2
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try
Sub Macro2()

    With Columns("G:G")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=COUNTIF(G1:$G$1,G1)>1"
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
    End With
End Sub

Open in new window

Regards
0
 
KimputerCommented:
Somehow, above code didn't work.  In that case, you might wanna try this alternative code:

Sub ColGYellow()

  maxrow = ActiveSheet.UsedRange.Rows.Count
  For i = 1 To maxrow Step 1
    If i <> maxrow Then
        If Cells(i, 7).Value = Cells(i + 1, 7).Value Then
            Cells(i + 1, 7).Interior.Color = 65535
        End If
    End If
  Next
  
End Sub

Open in new window

0
 
Rgonzo1971Commented:
@KiMputer
Maybe because the formula must be a local formula.
On my computer, it works perfectly
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
gwh2Author Commented:
Thanks for the replies,

Rgonzo1971: I tried your code but it doesn't seems to work correctly, ie. for the first lot of 3 duplicate rows, it highlighted all 3 rows, for the second lot of 2 duplicate rows, it highlighted the second one but not the first, then for the next set of 2 duplicate rows, it highlighted all 2 rows. In another row, it highlighted a name which was not a duplicate.

Kimputer: I tried your code and it highlights all duplicates except the first one. I wondered is it possible for the code to highlight all the rows including the first one?

Thank you both so much for the code.
0
 
KimputerCommented:
Include the first original turning yellow:

Sub ColGYellow()

  maxrow = ActiveSheet.UsedRange.Rows.Count
  For i = 1 To maxrow Step 1
    If i <> maxrow Then
        If Cells(i, 7).Value = Cells(i + 1, 7).Value Then
            Cells(i, 7).Interior.Color = 65535
            Cells(i + 1, 7).Interior.Color = 65535
        End If
    End If
  Next
  
End Sub

Open in new window

0
 
Rgonzo1971Commented:
then try
Sub Macro2()

    With Columns("G:G")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=COUNTIF(G:G,G1)>1"
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
    End With
End Sub

Open in new window

0
 
gwh2Author Commented:
Thanks again - both blocks of code work great.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now