Knowledge Base

How to Hide Records with Duplicate Cell Entries

Article ID: 147640

Article Last Modified on 8/17/2005

APPLIES TO

This article was previously published under Q147640

SUMMARY

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

In Microsoft Excel, 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 would 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: Using the Unique Records Only option in the Advanced Filter dialog box alone fails to eliminate the multiple records because the records are not exact duplicates.

MORE INFORMATION

To use the Advanced Filter feature to hide records with duplicate fields, do the following:
  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 contain the types of fields 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 and last name, select the columns that correspond to the first and last name, and the address, or 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 will be filtered in place. If you specified the first name column in the List Range box, only one record per unique first name will appear. You can copy these records and paste them to another worksheet that can be used as a merge data source document.

Additional query words: 5.0 5.00a 5.00c 8.00 97 98 XL98 XL97 XL7 XL5 database XL

Keywords: kbhowto KB147640


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.