We help IT Professionals succeed at work.

Find and count duplicates in Excel using Macro / vba

nainil
nainil used Ask the Experts™
on
I have an excel sheet with multiple columns. What I want to do is:

Scan Column B
Find duplicates
Highlight duplicates in Yellow (or any color)
Provide a count of the duplicate values (Yellow highlighted).

I have the below code. It highlights the cells as soon as I enter and filters duplicates.

I would want to have the filtering happen only after user clicks on a button. Ofcourse, I would like to have a count of these duplicates as well.

 
Sub ValidateData()

    If (ActiveSheet.Name = "Codes") Then
        MsgBox ("HHHHIII")
        Sheets("Codes").Columns("B:B").Select
        Selection.FormatConditions.AddUniqueValues
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        Selection.FormatConditions(1).DupeUnique = xlDuplicate
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = True

        
'        ActiveSheet.Range("Q18").HorizontalAlignment = xlCenter
'        ActiveSheet.Range("Q18").Value = cntDuplicates
'        ActiveSheet.Range("Q18").AddComment(str + " contain duplicates").Shape.TextFrame.AutoSize = True
        
'        str = ""
'        cntDuplicates = 0
        ActiveSheet.Range("A1").Select
    End If
   
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
complete code
Duplicate-Records.xlsm

Author

Commented:
@ukerandi:

This is excellent. I am trying to understand the code as to what it does and how is it getting duplicates.

I see how the counts happen (i think), but, I am not sure what you have used to find the duplicates.

The solution is terrific and i believe it works as expected, but just curious on how it works.

Commented:
hi i'm using Countif Function to find out how many same records available in the Range. if any records more than 1 it will color the cell.
Example
A                      B
test                12
test1               2
test3
test 4

Commented:
hi i'm using Countif Function to find out how many same records available in the Range. if any records more than 1 it will color the cell.
Example
A                      B
test1      5
test2      5
test3      5
test4      5

if i used Countif function for
=countif(A1:A4,"test1")
its showing only one "test1" in that range