XL3/XL4: Extracting Every "nth" Record from a Database

ID: Q51545

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x
Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x

SUMMARY

In Microsoft Excel, you can select every "nth" record from a database using the Extract command (on the Data menu) with a computed criteria.

For example, suppose you want to extract every fifth record from the following database defined as A3:B25 to use as a sample for the total population:


        A       B
   1
   2
   3   Test   Score
   4   1st     73%
   5   2nd     67%
   6   3rd     89%
   7    .       .
   8    .       .
   9    .       .

To do this, first create a computed criteria in A1:A2. To tell Microsoft Excel this will be a computed criteria, type any name in cell A1 other than the name of one of the database fields (for example, "Computed"). When using computed criteria, the criteria cannot be labeled with a field name from the database. In cell A2, type a formula to return every nth (5th) row of the database. For example, the following formula will extract every nth record of the database whose first record is in row y:


   =MOD(ROW(A4),n)=y

In our example, we would use the following


          A
   1     Computed
   2   =MOD(ROW(A4),5)=4

since we are extracting every 5th record from a database whose first record begins in row 4. The reference to A4 is a reference to the first record in the database and is incremented by Microsoft Excel as it steps through the database. Now, do the following:

Highlight A1:A2 and click Set Criteria on the Data menu.

Set up the extract range. For this example, to extract only the scores, place the field name "Scores" in cell C1.

With the extract range (cell C1) highlighted, click Extract on the Data menu. The records for cells A4, A9, A13, A18, and A23 should appear in the extract range.

MORE INFORMATION

For more information on computed criteria, query on the following words:


   computed and calculated and criteria

Additional query words: 1.00 1.03 1.04 1.06 1.50 2.20 3.00 4.00


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

Last Reviewed: March 15, 1999