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

• 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))

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

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

   excluding and list and records