Excel 2007 Filter for unique values or remove duplicate values

Filtering unique values and deleting duplicate values are two closely related tasks because the results shown are the same: a list of unique values. But they are still very different: when you filter a unique value, you temporarily hide the duplicate value, but when you delete the duplicate value, the duplicate value is permanently deleted.

A duplicate value is a value that exactly matches all of the values in the row and all the values in the other row. A duplicate value is determined by the value displayed in the cell, and it does not have to be the value stored in the cell. For example, if the different cells have the same date value, one format is "3/8/2006" and the other is "Mar 8, 2006", the value is unique.

It is best to filter the unique values so that you can confirm the results before deleting duplicate values.

Filter unique values

1, select a range of cells, or make sure that the active cell is in a table.

2, on the Data tab, in the sorting and filtering group, click Advanced.

3. In the Advanced Filter dialog box, do one of the following:

1 to filter the range of cells or tables in place, click Show Filter results in an existing area.

2 to copy the filter results to another location, do the following:

• Click "Copy filter results to another location."

• In the Copy to box, enter the cell reference.

You can also click the Compress dialog box to temporarily hide the dialog box, select the cell on the worksheet, and then press Expand dialog.

4, select the Select Not duplicate record check box, and then click OK.

The unique values in the selected range are copied to the new location.

Delete duplicate values

When you delete duplicate values, only the values in the range of cells or tables are affected. Any value other than a range of cells or a table is not changed or moved.

Note Because you want to permanently delete data, it is a good idea to copy the original cell range or table to another worksheet or workbook before you delete the duplicate values.

1, select a range of cells, or make sure that the active cell is in a table.

2, on the Data tab, in the Data Tools group, click Remove Duplicates.

3. Do one or more of the following:

• Under Columns, select one or more columns.

• To quickly select all columns, click Select All.

• To quickly clear all columns, click deselect Select All.

If a range of cells or a table contains many columns, and you want to select only a few columns, you will find it easier to click Deselect all, and then select the columns under Columns.

4, click "OK".

A message appears indicating how many duplicate values were deleted, how many unique values were retained, or whether duplicate values were not removed.

5, click "OK".