Three common techniques for Excel to remove duplicates

Three common techniques for Excel to remove duplicates

In other scenarios, users may want to find and remove duplicates that are the same but not completely duplicated in a few fields, such as the contents of the "Name" field in line 7th and row 12th in the following figure, but the contents of the other fields are not exactly the same.

These two types of duplicates are different, in the elimination of the implementation of the operation is slightly different, but there is no significant difference in nature, you can learn from each other reference. These two scenarios, for example, describe the three common techniques for removing duplicates in Excel.

Use the Remove Duplicates feature to remove duplicates in Excel

The Remove Duplicates feature is a new feature later in Excel version 2007 and is therefore available for Excel 2007 and subsequent versions.

The Delete Duplicates dialog box appears when you position the active cell in the list, and then click Data-Delete duplicates-on the ribbon.

The dialog box asks the user to select the column (field) where the duplicate data resides. Assuming that we define duplicates as exactly the same as the contents of all the fields, then all the columns will be checked. If you just define a list of identical records as duplicates, such as the second scenario mentioned at the beginning of the article, just tick the column field.

When all the columns are checked, clicking the OK button automatically gets the list of items that are deleted after the duplicates are removed, and the blank lines that are eliminated are automatically filled with the data rows below, but do not affect areas other than the datasheet. The effect is shown in the following illustration:

Use advanced filtering to remove duplicates in Excel

Prior to the release of version 2007, the Advanced Filter feature was a powerful tool for removing duplicates.

The Advanced Filter dialog box appears when you position the active cell in the list, and then click Data on the Ribbon--advanced (The Action path in version 2003 is data--filter--Advanced filter).

The dialog box will require the user to specify the list area, which is the range of cells in which the list is located, which is automatically generated by default. Generally select "Copy filter results to another location" to facilitate removal of duplicates after the processing operation. When this is specified, the dialog box will require the user to specify where to "copy to", that is, after the list is dropped after the duplicates have been deleted, the position of the cell in the upper-left corner of the user is specified, and in this example we are set to E1 cell. The last item is also one of the most critical options for removing duplicates you must check: "Select Not duplicate records." As shown in the following illustration: