Sampling Program

M

Miloann

Is there existing macros or functions that I can perform the following
sampling logic?

I have a file with 500K transactions. Would like to start with a number and
select one transaction every 30 records. The total number of selected
transactions are 2000.

Thanks.
 
M

Max

One way ..

Assume txn data is in col A, from A1 down to A65536

In B1 will be input the row start number, say: 3

Put in C1: =OFFSET(INDIRECT("A"&$B$1),ROW(A1)*30-30,)
Copy C1 down to C2000

C1:C2000 will return what's in: A3, A33, A63, ....
 
M

Miloann

Thanks a lot.

What if I want to save the extracted records into a new spreadsheet? Thanks
again.
 
M

Max

What if I want to save the extracted records into a new spreadsheet?
Click to expand...

Assume source txn data is in Sheet1, from A1 down

In another sheet, say Sheet2:

Input the source sheetname in A1: Sheet1
Input the row start number in B1, say: 3

Then we could put in C1, the revised:
=OFFSET(INDIRECT("'"&$A$1&"'!A"&$B$1),ROW(A1)*30-30,)
and copy C1 down to C2000, as before

The above will extract the required results
 
Top