Build Professional - Unbreakable - Forms in Excel

45 Tutorials - 5+ Hours - Downloadable Excel Files


Add to Favorites Favorited
*

How to lớn delete duplicate values from a data set in all versions of Excel. This includes Excel 2003 và earlier and also Excel 2007 and later.

Bạn đang xem: Find duplicate entries in excel 2003 column

For Excel 2007 and later, we can use a simple feature within the program, but for Excel 2003 and earlier, we must use a macro, which is provided below.

Sections:

Remove Duplicates in Excel 2007 và Later Versions

Remove Duplicates in Excel 2003 và Earlier Versions

Notes

Remove Duplicates in Excel 2007 and Later Versions

Select a cell within the data set that contains the duplicates:
*
Go to lớn the Data tab and then click Remove Duplicates:
*
You should now see a small window open. (Also, Excel should have selected the entire data set, in our example, A1:B6; if it did not select the correct data set, then you need khổng lồ hit the Cancel button và select the entire data set yourself before clicking the Remove Duplicates button.) In the small window, you need to lớn select which column you want to kiểm tra for duplicates. You can select one column to kiểm tra or multiple columns. If there are headers or titles for your data & those were also selected by Excel, then click the checkbox next to where it says My data has headers.
*
In this example, my data does not have headers and I only want to search Column A for duplicates, so I uncheck Column B and then hit OK.
*
Excel will remove the duplicates và you will see a window telling you how many duplicates were removed.
*
Here is the final result:
*

Notice that Excel removed the entire row of data & not just the data in Column A.

If you had selected Column A và Column B in Step 3, then no value would have been removed. This is because, Excel looks at all the values in all the selected columns khổng lồ see if there are duplicates across them instead of individually in each column. That is important if you want khổng lồ perform more complex duplicate removal in Excel.

If you made an error, hit Ctrl + Z on the keyboard to lớn undo the duplicate removal và start over.

Remove Duplicates in Excel 2003 và Earlier Versions

To remove duplicate values from Excel 2003 and earlier versions, we need lớn use a Macro.

Here are the steps to lớn install & use the macro:

When in Excel, hit Alt + F11 to lớn go to the VBA Editor window.

Xem thêm: Lời Bài Hát Em Sẽ Là Người Ra Đi Lyrics, Lời Bài Hát Em Sẽ Là Người Ra Đi

*
Go lớn Insert > Module
*
You should now see a small window xuất hiện and that is where we will input đầu vào the macro:
*
In that window, copy & paste this macro:

Public Sub DeleteDuplicateRows()" This macro will delete all duplicate rows which reside under" the first occurrence of the row.""Use the macro by selecting a column to check for duplicates"and then run the macro and all duplicates will be deleted, leaving"the first occurrence only.Dim R As LongDim N As LongDim V As VariantDim Rng As RangeOn Error GoTo EndMacroApplication.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualSet Rng = Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Columns(ActiveCell.Column))Application.StatusBar = "Processing Row: " và Format(Rng.Row, "#,##0")N = 0For R = Rng.Rows.Count lớn 2 Step -1 If R hack 500 = 0 Then Application.StatusBar = "Processing Row: " và Format(R, "#,##0") over If V = Rng.Cells(R, 1).Value If V = vbNullString Then If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then Rng.Rows(R).EntireRow.Delete N = N + 1 end If Else If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then Rng.Rows(R).EntireRow.Delete N = N + 1 over If kết thúc IfNext REndMacro:Application.StatusBar = FalseApplication.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomaticMsgBox "Duplicate Rows Deleted: " & CStr(N)End SubIt should now look something lượt thích this:

*
(If there was a problem performing the copy/paste from this site, make sure the single quotation marks in front of the green text are indeed basic normal single quotation marks; you can bởi this by deleting và retyping them in the VBA Editor window here. Or, just download the sample tệp tin attached khổng lồ this tutorial & copy the macro from there.)Hit Alt + F11 to go back khổng lồ the Excel window và select a cell within the column of duplicate values that you would lượt thích to remove:
*
Hit Alt + F8 khổng lồ see the macro.
*
Select the one called DeleteDuplicateRows and then hit the Run button. Once you vị that a small window will appear telling you how many duplicates were removed:
*
Hit OK and you"re done!
*
This method works pretty much the same, though is not as robust, as the Remove Duplicates feature for Excel 2007 và later.

You cannot "undo" this kích hoạt by hitting Ctrl + Z or anything else, so, be aware that whatever the macro removes is irreversible. Have a backup of the original data made before running any macro in case an error happens.

If you want to lớn keep this macro in your workbook, make sure khổng lồ save the workbook in a format compatible with macros; when you go to save the file, Excel will tell you if there is an issue, and, if you don"t see a warning, it should save just fine in the spreadsheet.

Follow this tutorial khổng lồ run the macro when you click a button in the worksheet.

Notes

If you are in Excel 2007 & later, use the built-in method to lớn remove duplicates since it is so much easier compared to having khổng lồ install a macro và since you can undo the kích hoạt if you make a mistake.

If you use the macro, you cannot undo any changes it makes lớn the worksheet; always make a backup of your data before running a macro.

Be careful removing duplicates on spreadsheets with a lot of data spread-out throughout the worksheet because it might remove data to the right or left of your current data set. The best thing to bởi vì is lớn have a separate worksheet that only contains the data that you want to check for duplicates. This way, you are certain khổng lồ not mess with any other data.

Make sure to download the accompanying spreadsheet for this tutorial. It contains the macro listed here & some sample data for you to lớn try everything out.