XL: Using MATCH() in Computed Criteria to Extract Unique Records |
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 :
Technology : kbvcSearch
Last Reviewed: July 29, 1999 |
