Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How to Hide Records with Duplicate Cell Entries

View products that this article applies to.


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.

↑ Back to the top

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.

↑ Back to the top

Keywords: KB147640, kbhowto

↑ Back to the top

Article Info
Article ID : 147640
Revision : 4
Created on : 8/17/2005
Published on : 8/17/2005
Exists online : False
Views : 402