Microsoft KB Archive/78022

{| = Excel: Database Extract Only Extracts One Record =
 * width="100%"|

Last reviewed: November 3, 1994

Article ID: Q78022 The information in this article applies to:
 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
 * Microsoft Excel for OS/2, versions 2.2, 2.21 and 3.0

SUMMARY
To extract records from a Microsoft Excel database, the database, criteria and extract ranges must be separate. If you attempt to extract records into the criteria range, the first record will be extracted, whether it meets the criteria or not. Other records that match the first record may also be extracted.

WORKAROUND
To avoid this behavior, copy the database field headings to a third location and and define them as the extract range.

MORE INFORMATION
The Extract command clears the cells below the extract range before performing the extract. This process creates a blank criteria range, causing the first record to be extracted. The procedure is then repeated, but because there is now data in the criteria range (the first record), no more records are extracted unless the database contains duplicates of the first record.

Example
  Enter the following in a worksheet: A1: NAME               B1: AGE A2: Tom                B2: 30 A3: Jane               B3: 40  Select the cell range A1:B3. From the Data menu, choose Set Database. Select the cell range A1:B1. From the Edit menu, choose Copy. Select cell D1. Press the ENTER key to paste the field names. Enter &quot;40&quot; (without the quotation marks) in cell E2. Select the cell range D1:E2. From the Data menu, choose Set Criteria. Select the cell range D1:E1. From the Data menu, choose Extract.

Only the first record in the database is extracted.