HOW TO: Hide Records with Duplicate Cell Entries in Excel 2000 (214247)

SUMMARY

This step-by-step article shows you how to use the Advanced Filter feature to hide records with duplicate cell entries in Microsoft Excel 2000.

In Excel 2000, you can hide rows that contain multiple records for an individual or company. Multiple records for the same individual or company may exist if you are tracking multiple transactions for the same individual or company. (These records may only differ from one another by several fields, but do not exactly duplicate one another.) However, when you generate a mailing list from the data, you want only one record per individual or company to appear. You can eliminate the redundant records by using the Advanced Filter feature in Microsoft Excel.

NOTE: If you use the Unique records only option in the Advanced Filter dialog box alone, you do not eliminate the multiple records because the records are not exact duplicates.

back to the top

Hide Records with Advanced Filter

To use the Advanced Filter feature to hide records with duplicate cell entries, follow these steps:

1. On the Data menu, point to Filter, and then click Advanced Filter.
2. In the List range box, type or select the range of cells that contains the types of fields that you want to filter. For example, if your worksheet contains the following data

      A1: Last   B1: First  C1: Address     D1: City     E1: State
      A2: Jones  B2: James  C2: 132 Skyway  D2: Skagway  E2: AK
      A3: Jones  B3: Bill   C3: 135 Skyway  D3: Nome     E3: AK
      A4: Jones  B4: Frank  C4: 139 Skyway  D4: Snaklut  E4: AK
      A5: Jones  B5: Bill   C5: 135 Skyway  D5: Aloot    E5: AK
      A6: Jones  B6: James  C6: 137 Skyway  D6: Siberia  E6: AK
   						

   and you want to filter individuals with the same first name, select the column that corresponds to the first name: cells B1 to B6. If there are different individuals with the same first name and last name, select the columns that correspond to the first and last name and the address: cells A1 to C6.
3. Leave the Criteria range box empty.
4. Select the Unique entries only check box, and then click OK.

By default, the records are filtered in place. If you specified the first name column in the List range box, only one record per unique first name appears. You can copy these records and paste them to another worksheet that can be used as a merge data source document.

back to the top