Microsoft KB Archive/51545
XL3/XL4: Extracting Every "nth" Record from a Database
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
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:
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.
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
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