Using a Macro to Find Duplicates in MS Excel?

Using a Macro to Find Duplicates in MS Excel?
Page content

Three Methods

Using Microsoft Excel opens up a welath of data manipulation options, not least the power to find errors and inconsistencies - such as duplicate records.

If you’re looking for duplicate entries in an Excel spreadsheet, there are three ways in which this can be done:

  • Cell-based formula
  • Macro
  • Toolbar option (Excel 2007 only)

Each of these methods has its own strengths and appropriate usage - we’ll look at each in turn so that when you need to make a choice you have as much information about that method as possible.

The Formula Method

A cell based script can be created and used to compare cells in the same row relatively easy. This is done by using an IF statement that queries whether two cells (adjacent or otherwise) are alike and then either displays a message or i

For instance the accompanying table features two columns of figures. Some are alike. By entering the following formula:

=IF(B4=C4, “duplicate”,0)

…we can highlight in a third column the rows that contain the duplicates. This formula can be copied down the column to bring the result for each row.

This formula can be used in a number of ways over any size or depth of table.

Using a Macro

Excel duplicate macro

The following method of utilizing a macro (a custom function) can be used in Excel 2002 Standard (released in Office XP), Excel 2003 and Excel 2007.

To create a macro, you’ll need go to the VB code screen and enter a macro in code form. In Excel 2007 this can be found by following these steps:

  • On the Office button, go to Excel Options.
  • Choose the Popular tab and tick Show the Developer Tab in the Ribbon; click OK.
  • In Excel, go to the Developer tab, and in the Code group of commands, click Macro Security.
  • To enable the macro to run, you’ll need to relax macro security. Under Macro Settings, click the entry Enable all macros (not recommended, potentially dangerous code can run), and then click OK.
  • Next, click on Visual Basic.
  • If required, on the Insert menu, click Module.
  • In the code window of the module, enter the macro code shown here (or copy Sample 1 from the Microsoft Support page).
  • Run the macro from the module window by pressing F5.
  • When you’ve finished with the macro, go to File > Close and Return to Microsoft Excel.

The range and worksheet number that is affected by the macro can be altered in these lines of the code:

iListCount = Sheets(“Sheet1”).Range(“A1:A12”).Rows.Count

Sheets(“Sheet1”).Range(“A1”).Select

Note that the script will not work on columns with empty cells.

The Toolbar Option

Finally there is the simple option for Excel 2007 users.

If your tables contain duplicate data in the same column, the Remove Duplicates option in the Data menu can be used to remove these entries.

To do this, you must first of all select the range of data you wish to check. You can either do this by dragging the mouse over these columns or by going straight to Data > Remove Duplicates and selecting the columns that Excel displays as containing data.

If your table has headers, make sure to tick the My Data Has Headers box in order for them to be ignored. Click OK to start the MS Excel 2007 Remove Duplicates tool. Within seconds all duplicates will be removed, and empty rows with them.

For more tips and tricks, be sure to browse through the collection of Microsoft Excel tutorials and user guides found here at Bright Hub. New and updated articles are being added all the time, so bookmark us and check back often.