Document number: 15160

 

This is a Level B document - It contains moderately difficult end-user information.

To delete duplicate rows in a Microsoft Excel worksheet, you must use a "filter". Click on a link below for more information:

• Filtering by copying to another location:
  Filters the data and then copies the filtered data to a specified location on the worksheet

• Filtering in place:
  Filters the data, but replaces the original data on the current worksheet. View the original data by removing the filter

• Notes and tips about filtering:
  See some specific issues that may arise when filtering

• Column labels/Headers:
  Information about column labels, including alert message

Note: This document gives just the basic filtering method. There are refinements that are not addressed in this document.

 

---

Filter and copy to another location

Follow these steps to filter the data and then copy that filtered data to another part of the worksheet:

1. In the spreadsheet you want to filter, click a cell that contains data. Only the data on the active worksheet tab will be filtered, and data in all columns and rows (except the header row) will be filtered.

2. On the Data menu, click on Filter, and then click Advanced Filter...

• If you get an alert message about the column labels, click here for more information.

3. On the Advanced Filter dialog box, click the radio button next to C opy to another location.

• If you wish to use the Filter the list, in-place option instead, click here.

4. In the Copy to field, type a cell reference.
   • This is where you want the upper leftmost location of the filtered data to be placed. For example, if your last row of original data ends at row 65, you could type in A67. The upper leftmost portion of your filtered data will begin in column A, two rows below your original data (this leaves one row blank between the original and filtered data so you can easily find the beginning of the filtered data.)
   • You can "browse" to the desired cell by clicking on the icon on the right side of the Copy to field: . Then navigate through the worksheet and click in the cell where you would like to upper leftmost cell of filtered data to appear. After you choose the cell, click on the icon to the right of the field on the now-collapsed window: .
   • Note: You can choose a cell in which data exists and thus paste over your original data, but the method as described here will leave your original data intact.

   • You may copy the filtered data only to the same active worksheet tab. You cannot specify another worksheet tab.

5. Select the Unique records only check box.

6. Click OK.

Your filtered data will appear starting in the cell you specified. You can move the filtered data to another worksheet tab or another file if you wish by copying (or cutting and pasting) the filtered rows to the new location.

 

 

Click here to go back to the beginning of this document.

 

 

---

Filter the list, in-place option

The filter the list, in-place replaces the original data with the filtered data on the active worksheet tab. The original data is preserved, but hidden.

1. In the spreadsheet you want to filter, click a cell that contains data. Only the data on the active worksheet tab will be filtered, and data in all columns and rows (except the header row) will be filtered.

2. On the Data menu, click on Filter, and then click Advanced Filter...

3. On the Advanced Filter dialog box, make sure the Filter the list, in-place option is chosen.

4. Select the Unique records only check box.

5. Click OK.

Your filtered data will appear, replacing the original data. The duplicate data is just hidden and can be seen again by clicking the Data menu and then Filter, then Show All. You can show either the filtered data or all the data; you cannot choose which rows are seen and which are not using the filtering method described here.

 

 

Click here to go back to the beginning of this document.

 

 

 

---

Notes and tips

• This document gives just the basic filtering method. There are refinements that are not addressed in this document.

• Be sure to back up your spreadsheet if you are not entirely comfortable or familiar with the filtering process. You may wish to try the filtering on a copy of the spreadsheet first.

• If filtering for exact duplicate rows, all of the data must be consistent throughout the spreadsheet.
• Every cell in each column must contain the same category of information in the same format ( i.e., last name, date, address, etc.)
• The filtering process described in the document will only remove rows where the data is exactly the same in each of their respective cells.

  • Example: If you have a spreadsheets where one column is for addresses, "123 - State Avenue" is not the same as "123 - State Ave."

• Try not to have blank rows in your data - they could cause some confusion, but the data will probably filter fine.

• Column labels/Headers:
  • Make sure a column label/header appears at the top of the columns. The filtering process will not work properly unless a header row exists, and every column must have a header label. The headers will be copied at the beginning of the filtered data.
  • If the following message appears, you should insert a header row. If you have already included a header row as the first row, just click OK and your data should filter. You may want to make sure that the first row of data is included properly after the data is filtered. If the data is not included, click on the Data menu, then Filter, and then Show All to remove the filter. Then close out of Excel. Relaunch Excel and see if the problem disappears when you filter again.

    

 

 

Click here to go back to the beginning of this document.

 

 

 

 

 

 

Created by the PeopleSoft Knowledge Management Team.
Copyright © 2002 All rights reserved.
Created: kcw 08/13/2002
Reviewed: 08/27/2002