Microsoft Excel 2007 tutorial: filter unique data


Filter unique data

II. Filter unique data

For example, you have entered duplicate data in a range of cells as the figure below:

Excel 2007 list names

You want this range of cells displaying only the unique data. To do this task, do the following:

1. Select the range of cells that contain duplicate data, and must include the headers of cells.

Excel 2007 selected range of cells

2. On Data tab, in the Sort & Filter group, click Advanced.

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

Excel 2007 Advanced Filter dialog box

   ▫ To filter data in the range of cells or table, click Filter the list, in-place.

   ▫  To copy the filtered data to another location, click Copy to another location.

    After you select the Copy to another location option button, the Copy to box is enabled so that you can enter or select the location (cell reference) that you want the filtered data to display. Note that you can copy the filtered data to the active worksheet only. Otherwise, the message box will displays to inform you that "You can only copy filtered data the active sheet".

Excel 2007 message box

4. In Criteria range box, enter the reference for the range of cells that you want to filter or select the reference by clicking Collapse Dialog Excel 2007 Collapse Dialog, then clicking the cell that you want to use as the criteria, and then clicking Expand Dialog Excel 2007 Expand Dialog to go back to the Advance Filter dialog box.

Excel 2007 filter data

5. Select the Unique records only check box, and then click Ok button.

The filtered data will display as the figure below:

Excel 2007 result of filter data

Another example uses the data in the range of cells in the example above to filter the unique data and then copy them to another location. Do the following:

1. Select the range of cells that contain duplicate data, and must include the headers of the cells.

2. On Data tab, in the Sort & Filter group, click Advanced.

3. In the Advanced Filter dialog box, click Copy to another location.

4. In Criteria range box, enter the reference for the range of cells that you want to filter or select the reference by clicking Collapse Dialog Excel 2007 Collapse Dialog, then clicking the cell that you want to use as the criteria, and then clicking Expand Dialog Excel 2007 Expand Dialog to go back to the Advance Filter dialog box.

5. In Copy to box, enter the reference that you want the filtered data to display or select the reference by clicking Collapse Dialog Excel 2007 Collapse Dialog, then selecting the range of cells that you want the filtered data to show, and then clicking Expand Dialog Excel 2007 Expand Dialogto go back to the Advance Filter dialog box.

Excel 2007 copy filtered data to another location

6. Select the Unique records only check box, and then click Ok button.

The filtered data will display as the figure below:

Excel 2007 result of copying filtered data

Note: The number of columns of the source data cells must be equal to the number of the destination cells . For example, if the data contain in two columns (A1:B5), you must select two columns (A6:B12 or C1:D5) for the destination cells too. If the destination cells that you select is not enough for displaying the copy filtered data, a message box will appear as the figure below:

Excel 2007 cell data and destination cell not eaqual

This message box informs you that "The destination range is not enough to accommodate all copied rows. Data below the destination range will be lost. Continue copying anyway?". Click Yes button to continue copying the data, or  click No button to stop the process of copying data.

If the data are stored in two columns but only one column is selected for the destination cells, Excel will display a message box as the figure below:

Excel 2007 missing field name

 


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.