Excel: Database Extract Only Extracts One Record

• 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

1. Enter the following in a worksheet:

         A1: NAME                B1: AGE
         A2: Tom                 B2: 30
         A3: Jane                B3: 40
   
   

2. Select the cell range A1:B3. From the Data menu, choose Set Database.

3. Select the cell range A1:B1. From the Edit menu, choose Copy.

4. Select cell D1. Press the ENTER key to paste the field names.

5. Enter "40" (without the quotation marks) in cell E2.

6. Select the cell range D1:E2. From the Data menu, choose Set Criteria.

7. Select the cell range D1:E1. From the Data menu, choose Extract.

REFERENCES

"Microsoft Excel User's Guide, Book 1," version 4.0, pages 327-343.

"Microsoft Excel User's Guide," version 3.0, pages 369-383.

"Microsoft Excel for Windows Reference Guide," version 2.1, pages 177-186.