You are currently offline, waiting for your internet to reconnect

HOW TO: Use Advanced Filter to Exclude Records in Excel 2000

This article was previously published under Q214324
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Excel 97 and earlier version of this article, see 183512.

IN THIS TASK

SUMMARY
This step-by-step article explains how to use the ISNA and MATCH worksheet functions to exclude records in a Microsoft Excel database that do not match another list.

back to the top

How to Create Exclusion Criteria

To create exclusion criteria, use the following formula
=ISNA(MATCH(firstcell,excludelist,0))
where firstcell is the first cell in the field of your database that you want to filter and excludelist is the list of entries that you want to exclude.

When you filter records in an Excel database, you usually specifycriteria that you want the records to match. However, when you use the ISNA and MATCH worksheet functions, you can create an advanced criteria that filters all records that do not match the criteria.

back to the top

Example

To use this example, follow these steps:
  1. Type the following values in a new worksheet:
       A1: Region  E1:         F1: Criteria   A2: 1       E2: 1       F2: =ISNA(MATCH(A2,$E$2:$E$3,0))   A3: 2       E3: 3       F3:   A4: 3       E4:         F4:   A5: 4       E5:         F5:   A6: 1       E6:         F6:					
  2. Click cell A1 (or any cell on the list).
  3. On the Data menu, point to Filter, and then click Advanced Filter.
  4. In the Advanced Filter dialog box, click Filter the list, in place.
  5. In the List Range box, type A1:A6.
  6. In the Criteria range box, type F1:F2.
  7. Click OK.
NOTE: When you create an advanced criterion formula that excludes records, the cell reference to the list of entries that you want to exclude must be an absolute reference.

When you specify an advanced criterion formula, the cell above the formulacannot contain an actual field name; it must either be blank or contain adifferent text string.

back to the top
REFERENCES
For more information about advanced filters, click Microsoft Excel Help on the Help menu, type filter a list by using advanced criteria in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

back to the top
howto computed extract XL2000
Properties

Article ID: 214324 - Last Review: 12/05/2015 12:33:26 - Revision: 3.1

Microsoft Excel 2000 Standard Edition

  • kbnosurvey kbarchive kbhowtomaster KB214324
Feedback