XL: Using MATCH() in Computed Criteria to Extract Unique Records

ID: Q111361

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0
Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a

SUMMARY

In the versions of Microsoft Excel listed above, you can extract unique records based on a particular field or combination of fields with a computed criteria that uses the MATCH() function.

MORE INFORMATION

When you extract records from a Microsoft Excel database, you can use computed criteria to evaluate one or more fields. The Extract Unique Records Only option, which you are prompted with when you click Extract on the Data menu, compares the contents of each record during the extract. Therefore, Microsoft Excel does not exclude records unless the entire record is identical to a record that has already been extracted. This procedure is similar to extracting only the first occurrence of a record, based on one or more database fields.

In the example below, if you enter the following formula into the computed criteria field (C2 in this example),

=ISNA(MATCH(A2,$C$6:$C$15,0))

Microsoft Excel extracts all records in the database, except those that are duplicate records, based on the CATEGORY field.

Example

In the following example, the database range is defined as $A$1:$B$10, the computed criteria range is defined as $C$1:$C$2, and the extract range is defined as $C$5:$D$5.


    A1: CATEGORY     B1: COST      C1:             D1:
    A2:     D        B2:  10       C2:  FALSE      D2:
    A3:     C        B3:  20       C3:             D3:
    A4:     A        B4:  30       C4:             D4:
    A5:     B        B5:  40       C5: CATEGORY    D5:  COST
    A6:     E        B6:  50       C6:   D         D6:   10
    A7:     F        B7:  60       C7:   C         D7:   20
    A8:     B        B8:  70       C8:   A         D8:   30
    A9:     G        B9:  80       C9:   B         D9:   40
   A10:     D       B10:  10     C10:    E        D10:   50
   A11:             B11:         C11:    F        D11:   60
   A12:             B12:         C12:    G        D12:   80

The Extract range displays unique records using computed criteria based on the CATEGORY field only. No other fields are evaluated for this extract and the first occurrence of the record is extracted based on criteria specified using the MATCH() function.

For more information on this process, query on the following words in the Microsoft Knowledge Base:

excluding and list and records

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.00a XL4 XL3

Keywords : xlformula
Version : WINDOWS:2.0,3.0,4.0; MACINTOSH:2.0,3.0,4.0
Platform : MACINTOSH WINDOWS
Issue type :