XL: How to Hide Records with Duplicate Cell Entries

ID: Q147640

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition
Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY

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.00 5.0 5.0a 5.00a 5.0c 5.00c 7.00 8.00 97 98 XL98 XL97 XL7 XL5 database

Keywords          : xlui 
Version           : WINDOWS:5.0,7.0,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: January 8, 1999