How to create/record and run macro for finding duplicates in excel

Macro represents a set of instructions which can be executed in a single command/trigger to perform specified actions. These are used to automate the repetitive tasks with in Microsoft office products like excel etc.

Microsoft word and excel provide a mechanism to record the steps performed and then link it to some triggering mechanism like button etc so that all of the recorded steps could be executed again just on a single trigger like click of button.

Today we will do the step by step demonstration of recording the macro to be used for finding the duplicate & corresponding number of occurrences and running the macro just on a single click. The sample excel initially had only one column having names and at the end of the exercise we had created a macro and a button for trigger. On the click of button, macro populates the duplicate names and corresponding count in second sheet.

The sample excel with macro created as a part of this exercise can be accessed from here.

In case you want to create the macro by coding rather than recording, the sample code would be like following -

Sub FindDuplicates()

' FindDuplicates Macro

    Sheets("Sheet1").Select
    Columns("A:A").Select
    Selection.Copy
    Sheets("DuplicateRecords").Select
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False
    Range("B1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Count"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B80")
    Range("B1").Select
    ActiveSheet.Range("$A$1:$B$80").AutoFilter Field:=2, Criteria1:=">1", _
    Operator:=xlAnd
    End Sub

Step by Step method of recording and running the macro

Steps#1 - Go to the sheet having the data and check that you have got developer option and macro record option available . (if not at first add this option to Ribbon)

Step#2- Create a new tab named as 'Duplicate Records' to display information around duplicate records.

Steps#3- Click on the record macro icon shown at the bottom of excel or the record macro button in 'Developer' tool bar,  this will show you the option of creating macro. Put the name of macro and click ok.

Step#4 - Perform the steps to find the duplicates 

Step#4-a-Copy the records manually from the first sheet

Step#4-b-Paste it to the second sheet named as 'DuplicateRecords' and add a new column called count

Step#4-c - Put the formula to find the count of the first record

Step#4-d - Extend the formula to the whole column

Step#4-e-Select the option of value getting filtered by Values

Step#4-f-Select the filter option excel giving you records having count >1

Step#4-g - The list having duplicates would appear now

Step#5 - Stop the macro recording

Step#6 - Save the excel as macro enabled excel & close the excel.

Step#7 - Open the excel again and enable the macro in excel

Step#8 - Create button based trigger for on demand execution of macro & associate the macro with it

Step#9 - Click the button to see duplicates (the Macro action repeated)