How do i sort rows randomly?

[Guest]

I want to choose 50 random rows from 10,000 lines of data and paste it into a
new sheet. The only way I know is to use a random number generator to
randomly select the records and then copy/paste the data out out, row by row,
fifty times, which is time-consuming. Is there a way to randomize my entire
data table by row so that I can take the first fifty rows all at once and
know that they've been randomly selected? Thanks. Jeremy

 

[Gordon]

I want to choose 50 random rows from 10,000 lines of data and paste it into a
new sheet. The only way I know is to use a random number generator to
randomly select the records and then copy/paste the data out out, row by row,
fifty times, which is time-consuming. Is there a way to randomize my entire
data table by row so that I can take the first fifty rows all at once and
know that they've been randomly selected? Thanks. Jeremy

Add a new column - random generate a number in every one of the 10,000
cells in that column, sort your data on that column and take the top 50
rows.

 

[Guest]

You could try inserting a column 'before' A.
On another sheet generate 5/10/20 whatever columns of random numbers
(1-10,000).

When you want to get your random data selection, copy/paste values any one
of these columns into column A of your data sheet, sort by column A, copy 50
rows.

How you randomly select the column you want - maybe roll a dice, assign a
column for each day, anything that seems pseudo random will do...

 

[RagDyeR]

To create an automatic random order generator, where sorting is unnecessary,
and where a new random order is displayed with every hit of the <F9> key,
try this:

With data in Column A, in Column B, or *any* out of the way column,
Enter this formula:
=RAND()
And copy down as many rows as there are rows of data in Column A.

Then, enter this formula where you wish to start the display of your random
selections:

=INDEX($A$1:$A$20,RANK(B1,$B$1:$B$20))

And drag down to copy as many rows as the number of random choices that you
wish to display.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I want to choose 50 random rows from 10,000 lines of data and paste it into
a
new sheet. The only way I know is to use a random number generator to
randomly select the records and then copy/paste the data out out, row by
row,
fifty times, which is time-consuming. Is there a way to randomize my entire
data table by row so that I can take the first fifty rows all at once and
know that they've been randomly selected? Thanks. Jeremy

 

[Guest]

That's terrific! Is it possible to have the randomized list appear on a
different sheet within the same workbook?

 

[Giles]

You can put the randomised list on another sheet of the same workbook simply
by adjusting the formula e.g.

=INDEX(SHEETS1!$A$1:$A$20,RANK(SHEETS1!B1,SHEETS1!$B$1:$B$20))

This assumes that the original location of your list is on "SHEET1", that it
covers cells A1-A20 and that the random numbers are in B1-B20.