A duplicate entry in Microsoft Excel is an entry in a column or row that matches other entries in the same column or row. You may want to remove these duplicate entries to tighten up your data, but you cannot do this manually, especially if you have hundreds of cells of data. Unfortunately, if you are working in Microsoft Office Excel, particularly Excel 2002 and Excel 2003, removing duplicate entries in columns or rows automatically is not obvious.
- Skill level:
Browse to Tools on the toolbar and down to Macro. To the right, find the Visual Basic Editor and left-click on it. Once in the Visual Basic Editor, find the drop-down that allows you to create a User Form, Module or Class Module. Choose "Module."
Copy and paste the following into the editing box that opens:
Sub DelDups_OneList() Dim iListCount As Integer Dim iCtr As Integer ' Turn off screen updating to speed up macro. Application.ScreenUpdating = False ' Get count of records to search through. iListCount = Sheets("Sheet1").Range("A1:A100").Rows.Count Sheets("Sheet1").Range("A1").Select ' Loop until end of records. Do Until ActiveCell = "" ' Loop through records. For iCtr = 1 To iListCount ' Don't compare against yourself. ' To specify a different column, change 1 to the column number. If ActiveCell.Row <> Sheets("Sheet1").Cells(iCtr, 1).Row Then ' Do comparison of next record. If ActiveCell.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then ' If match is true then delete row. Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp ' Increment counter to account for deleted row. iCtr = iCtr + 1 End If End If Next iCtr ' Go to next record. ActiveCell.Offset(1, 0).Select Loop Application.ScreenUpdating = True MsgBox "Done!" End Sub
Click "File" and then "Close and Return to Microsoft Excel." Once returned to Excel, browse to the Tools option in the toolbar and then to Macros. To the right, choose the "Macros" option. In the Macro option box, see the macro called DelDups_OneList. Choose "Options" and assign a shortcut key of "q." Click "OK."
Click "Run." "Done" will appear. The duplicate entries in the first column are deleted.
Because you have set up your shortcut key for Ctrl+q, you only need to type Ctrl+q in the future and the duplicate entries in your first column of data will be removed.
Tips and warnings
- Other macro codes find duplicates or remove duplicate rows. See Resources below for links to those macro codes.
- Because you are working with a macro that deletes data, back up your Excel document before applying the macro.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for