I have multiple worksheets containing between 200 - 4000 data items of which
I need to take a random sample of 50 and enter the details of these 50 only
in another worksheet. To obtain a random sample across the whole group, if
there were 200 files then I would select every fourth file. Currently I am
linking the first records, filling the series down to 50 records and then
manually amending every formula link to recognise the next row I wish to
select (eg. every fourth). Is there an easier way I can do this, (I cannot
sort the data in a way that the first 50 shown are the random sample.)

Any ideas would be much appreciated.

One way you could sort the data to isolate what you want.

Number your list 1 through however many data items you have (don't use a
formula - or if you do use copy/paste special-values to hardcode the
numbers). Then in a cell to the left of your numbers (say Column A) enter

=MOD(B1,4)=0

or

=MOD(B1+3,4)= 0

if you intend to select the first item, then every fourth.

Copy the formula down Column A as far as your data table.

This will return TRUE for every fourth item, FALSE for everything else. Now
sort with the TRUE/FALSE column (descending order). Then, copy selected
items to new worksheet. Sort your list by the numbered column (1 through
whatever) to put the list back in its original order). Or use Autofilter to
isolate the TRUE values and copy to a new worksheet.


"Twinkle17" wrote:

I have multiple worksheets containing between 200 - 4000 data items of which
I need to take a random sample of 50 and enter the details of these 50 only
in another worksheet. To obtain a random sample across the whole group, if
there were 200 files then I would select every fourth file. Currently I am
linking the first records, filling the series down to 50 records and then
manually amending every formula link to recognise the next row I wish to
select (eg. every fourth). Is there an easier way I can do this, (I cannot
sort the data in a way that the first 50 shown are the random sample.)

Any ideas would be much appreciated.

I don't think I read your post carefully enough. You want the data linked to
the original worksheet?

In that case, on your new sheet, link the first row to your original data
table, then copy your links down however many rows you have. Then insert two
columns (index column and column w/the MOD formula) I described in last post
to identify the items you want. Use Autofilter and select FALSE values in
the first column. Select the filtered list (which should now show only the
FALSE values) and delete the entire row (Edit/Delete). Now turn off
Autofilter.


"Twinkle17" wrote:

I have multiple worksheets containing between 200 - 4000 data items of which
I need to take a random sample of 50 and enter the details of these 50 only
in another worksheet. To obtain a random sample across the whole group, if
there were 200 files then I would select every fourth file. Currently I am
linking the first records, filling the series down to 50 records and then
manually amending every formula link to recognise the next row I wish to
select (eg. every fourth). Is there an easier way I can do this, (I cannot
sort the data in a way that the first 50 shown are the random sample.)

Any ideas would be much appreciated.