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
0
Jeremy1 (114)
2/18/2005 3:55:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
453 Views

Similar Articles

[PageSpeed] 27

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
0
gordonbp11 (453)
2/18/2005 3:57:13 PM
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
0
2/18/2005 4:09:01 PM
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" <Jeremy@discussions.microsoft.com> wrote in message
news:475385A6-C680-4813-B0F4-E4393423AD97@microsoft.com...
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


0
ragdyer1 (4060)
2/18/2005 4:22:10 PM
Reply:

Similar Artilces:

Sorting data
Hi Is it possible to group data together so that it stays together when I sort it alphabetically? I'd like to make a list for example, Police, Fire and Ambulance stations. In cell a1 would be Police followed in b1, b2, b3, etc the names of the stations. Then in c1,c2,c3 etc the relevant telephone numbers. Then in a 4 would be Fire, followed by b4, b5, b6 etc the names of the fire stations and then their tel numbers in c4, c5, c6 etc.The same applies to Ambulance. Then I'd like to sort col A and keep col b and col c together under the proper emergency service Hope you can underst...

Checking for duplication on rows
Hello, In a particular spreadsheet, I would like to verify if the same name is listed on multiple rows. The name is entered in multiple columns on the same row, that is acceptable, but I would like to know if the name is entered on multiple rows regardless of the column. How can I check this? THANKS, karmen -- Karmen ------------------------------------------------------------------------ Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972 View this thread: http://www.excelforum.com/showthread.php?threadid=509495 Presume you're checking some...

Keeping Row 1 At Top When Scrolling?
How do i make it so that the top row, row 1, stays at the top of my sheet no matter how far i scroll down the page? Many thanks Kev. Kev, Select cell A2 and then go to the Window menu and choose Freeze Panes. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "KMc" <mcauley@ourgeneration.freeserve.co.uk> wrote in message news:bf0re3$uoe$1@newsg4.svr.pol.co.uk... > How do i make it so that the top row, row 1, stays at the top of my sheet no > matter how far i scroll down the page? > > Ma...

Count Rows When Using Auto-Filter
Using the auto-filter function I need to count the number of rows returned each time I select a new value. I can do a COUNTA (&#8211;1 to allow for the header row), on the column that counts all the rows but when I use the filter I still get the total count not a count of the reduced quantity by the filter. Can anyone help? Nick use the subtotal function with a first argument of 3 =subtotal(3,A2:A300) as an example. Make sure the column you do the counta on will have values in all rows. -- regards, Tom Ogilvy "Nick" <ner@westnet.com.au> wrote in message news:15...

Ranking/Sorting more than 3 row's
I have a spreadsheet with 742 rows (of company names). I have 5 columns of criteria for the companies. Each column is a different criteria about each company, and has a numerical rank for each company. 1-~75 (some columns/criteria have no rank for a company) I want to create an overall rank of the companies based on how they perform across the 5 rows. I want the companies with the most 1's at the top and then those with 1 & 2's and then those with 2 & 2's etc. Is there a way to sort or rank this spreadsheet of companies? I would do a sum of the inverses of each of...

Hidden Rows
I came accros a spreasheet were the first 8 rows are hidden. I can select and edit the cells by entering the cell reference into th Name Box. But I cannot make them visible - the rows have a height o 15. And selecting all the cells then selecting unhide does not work. Protection has not been applied. Does anyone have any ideas on how this was done? and how can I mak them visible? Thanks -- michael.a ----------------------------------------------------------------------- michael.a7's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3302 View this thread: http:...

Switch row with column?
Hello guys, im very new to excel, and im trying to learn. I cant seem to figure out how to switch my chart around. I have a list of companies on the top, and down the left i have a list of locations. How can i make the locations go on top, and the companies down the left? -- jason57gizmo ------------------------------------------------------------------------ jason57gizmo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30700 View this thread: http://www.excelforum.com/showthread.php?threadid=503627 Depending on the number of rows you're using--since t...

Calculate difference in rows in a query
I have a query that calculates how much revenue is claimed by month per job but then I have to take the new month less the prior month. The problem is the data is in row format. I don't know how to subtract February from January, March from but don't know how. I know how to do it in Excel but not Access. Please help... Example: Order Month JTD Clm Variance 101026521 January $511,525 $0 101026521 February $511,525 $0 101029438 January $1,238 $0 101029438 February $3,713 $2,475 101033168 January $21,465 $0 101033168 February $51,460 $29,995 101034011 Janu...

excell sorting question
does anyone know how to do a sort for how close a list of numbers com to a designated cell with a index number in that cell?? example: a 39 b 40 c 51 d 44 e 43 sort by being the same or the closest to "44" results should be: d 44 e 43 b 40 a 39 c 5 -- Message posted from http://www.ExcelForum.com Twosix, You can add a helper column... In the column next to that one (assume A1:A5) enter =ABS(44-A1) and drag down to fill the series. Select both columns and choose Data -> Sort and sort according to the helper column. Dan E "twosix >" <<twosix...

Sorting by Text field of numbers
I am using Access 2003. I have created a database to help me files by "Lot#'s". Our lot numbers for one of our products is all over the place so I created the Lot # field as a text field. Ex: 02-01-01, 10000272925, 10-06-02-04-06 and 1503011 A1-02. Why does access put - 1000272925 before 10-06-02-03-05? Does this look right to anyone? My thinking is that "10" comes before "1000272925". What affect does the "-" have in sorting the numbers. Would I be better off to put a space instead of the "-"? Can anyone help me ...

Insert blank rows
Rather than using the control button to select every other row, which can be very time consuming when you have more than 100 rows, I am trying to figure out how to auto-insert blank rows between pre-populated rows of information. Deb, Try this macro. ALT+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code in on the right . Change the sheet name to your sheet and run the code Sub insert_rows() Dim ws As Worksheet, x as long Set ws = Sheets("Sheet3") lastrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row For x = ...

Help with counting rows
I have a datafeed that comes to me everyday via email , every day the feed had slighty different rows as it has different updated products i.e. somedays 999 , 1001 etc. What i need to do is run a macro so when the excel file arrives to count the number of rows and then add the letter "y" in a 3 new columns . At the moment i have to do it manually is this easy to do Paul iLastRow = Range("A1").End(xlDown) Range("B1").Resize(iLastRow,3).Value = "y" -- HTH Bob Phillips (remove nothere from email address if mailing direct) <plm11111@h...

Insert row, table to table
Hi, As I cannot use INSERT INTO with values specified to WHERE. I have created an interim temporary table where the record contains two text strings.Now I want to be able to take those text strings and append to the correct table where the record is set by an ID number. First_Name = Fred Last_Name = Jones Its a new contact so it is too be added to a customer with an ID = 1375 Now I can't use the following sql statement strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _ & " VALUES (""" & strFirst & "&...

Insert a row based on a macro
Is there a way to insert a row based on a formula. For example, If A5=25, I then want to duplicate the existing row and insert it in the line below. See answers in your other post. Pls don't multi post -- Don Guillett SalesAid Software donaldb@281.com "Mindie" <Mindie@discussions.microsoft.com> wrote in message news:75A1DDD5-6334-410D-8646-091B43D1B198@microsoft.com... > Is there a way to insert a row based on a formula. For example, If A5=25, I > then want to duplicate the existing row and insert it in the line below. ...

insert 'x' number of rows in excel
Hi, I'm looking to insert 'x' no. of rows into an excel document where 'x' is the result of a 'countA' function. Is this possible? Hi# only possible with VBA. Would this be feasible for you? >-----Original Message----- >Hi, >I'm looking to insert 'x' no. of rows into an excel document where 'x' is >the result of a 'countA' function. > >Is this possible? >. > Yes VBA is feasible "Frank Kabel" wrote: > Hi# > only possible with VBA. Would this be feasible for you? > > >-----Original ...

Preserving formulas when using sort function
Hi If I have a two columns of data say, A and B and the are added together in rows in Column C eg A1 + B1 = C1 A2 + B2 = C2 etc If I then move the contents of cell A1 and put it in say A23 the value of C1 does not change because the C1 become A23 + B1. However, if I sort Column A then the value of C1 changes and the formula appears to be wrong because it now takes the new value of cell A1 How can I make sure that when the sort takes place that wherever the value in A1 ends up it is still added to B1 to create a value in C1? Any help appreciated Hi Andy, Why aren't you moving th...

Sort numbers with text in ascending order
I am having a difficult time sorting the following list: MY List How Excel Sorts I Need 2148-00 1472-00 171B-00 1480C-00 1480B-00 171C-00 1480B-00 1480C-00 1472-00 1472-00 171B-00 2148-00 171C-00 171C-00 1480B-00 171B-00 2148-00 1480C-00 I have tried formatting numbers as text and other options. I am at my wits end now. Is this sort even possible? Please help. Tania TVOUK Where is the logic to your sort please? If you can identify that then a sort may be possible! ...

Excel opening a "phantom" worksheet at random times
I have a user who will have a phantom Excel spreadsheet, entitled Object, open at random times while she is trying to open another spreadsheet. This Ojbect spreadsheet will stay open, and she can open other spreadsheets, but the only thing that will open is the Menu bar, toolbars, and the status bar. The file itself doesn't open, the space is empty and the user can see the desktop. When this happens, the user has to shut down her machine, not a restart, before she can continue on in Excel. Any ideas? ...

Offset, sum down to the first blank row
can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. "Tami" <Tami@discussions.microsoft.com> wrote in message news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com... > can anyone write the formula to ...

Numbers won't sort correctly.
I have a column of numbers that were imported from a web site and they won't sort correctly. I have tried reformatting the cells as general numbers but that doesn't change anything. I discovered that numbers below 100 have a space or invisible data in front of the number. If I remove it, the number then shifts to right alignment in the cell and will sort as a number. Is there any way I can remove these spaces from all numbers at once instead of going down the column row by row? Hi Fern One way Type a 1 into a blank cell on your sheet. Copy the cell. Mark the range of your data...

Report Writer Dexterity Error
I get a "Dexterity Runtime has encountered a problem and must close" error when I try to run one particular modified report. The error occurs when I add a sort function in Report Writer. When I remove the sort, the report runs - without error. I am on GP 9.0. ...

How to create a random number between a range?
I need to generate random whole numbers between the range of 21 and 90. I started with this: =rand()*90 with the cells formatted to zero decimal places. how do I limit the lower number? Any help here will be appreciated. Thanks in advance dave =RAND()*(b-a)+a taken from the Excel help file. So in your case RAND()*(90-21)+21. If you want integers only then =INT(RAND()*(90-21)+21 In Excel 2007, = RANDBETWEEN(21,90) Tyro "Dave" <dave@accessdatapros> wrote in message news:56FD1420-58DC-4385-A784-C9964269B8F3@microsoft.com... >I need to generate random whole numbers...

Moving Column Sums to Rows
What is the simplest way to enter a sequence of values such as =SUM (A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN? What is the simplest way of converting simultaneously such values to ($a$1:$a$100) etc.? I could only find a slow and clumsy method by putting the formula in a row, right dragging, then using F4 on each individual cell before using paste special with transpose. Roger PB Hi Roger, > What is the simplest way to enter a sequence of values such as =SUM > (A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN? > =SUM(OFFSET($A$1,0,ROW()-1,1,100...

Generate random sequence based on probability
I have a list of events and the probability that each will happen. Example: Event A 12% Event B 20% Event C 2% ...and so on. My list is about 20 entries long. What I want to do is to have Excel randomly determine what event will happen in what order, based on the percentage chance that it will happen. So I want 20 cells in another column with the results looking something like this: Event K Event B ...and so on. And be able to recalculate upon refresh. Thanks in advance!! Assuming the list is in A1:A20 - In cell C1 type: =INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW()),$...

Excel won't allow access to some rows!--- Very strange!!
I have a strange problem with one of my spreadshteets. I am not able to access or see any rows 1 to 31. It is as if the "hide" function is in effect. I have tried the "unhide" function, but no use. It is as if I am locked out of these rows! I don't have any "freeze" function active with respect to the unaccesible rows. Anyone here knows what is going on? thanks When you try and unhide select the whole sheet by clicking the border above and to the left of cell A1 and then try unhide rows and then also try row heightr standard. -- Hope this helps M...