Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do i sort rows randomly?
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 |
#2
|
|||
|
|||
Jeremy wrote:
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. -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk |
#3
|
|||
|
|||
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... "Jeremy" wrote: 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 |
#4
|
|||
|
|||
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! ============================================== "Jeremy" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i sort rows randomly?
That's terrific! Is it possible to have the randomized list appear on a
different sheet within the same workbook? "RagDyeR" wrote: 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! ============================================== "Jeremy" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i sort rows randomly?
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. "Final TA" wrote: That's terrific! Is it possible to have the randomized list appear on a different sheet within the same workbook? "RagDyeR" wrote: 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! ============================================== "Jeremy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Randomly Sort a Series of Numbers | Excel Discussion (Misc queries) | |||
Why have I got some repeating rows after a Excel 2003 sort | Excel Discussion (Misc queries) | |||
Adding Rows to Master Sheet | New Users to Excel | |||
Can Excel sort rows by color? | Excel Discussion (Misc queries) | |||
I want to sort data randomly in addition to the current descendin. | Excel Worksheet Functions |