Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying random rows in excel to another sheet
Hello,
I have two issues 1) I have a sheet consisting of 40 rows and 9 columns. I want to randomly pick/cut 30 of these rows (not columns, because each column in each row is dependent to each other, therefore I need the randomly picked row to contain all its related columns) and paste them to a new sheet. I would also like to cut and paste the remaining 10 row to another sheet. I have tried the rand function but it only randomly picks a column. 2) I would also like to get some advice on approach. The reason for the randomising is that I need to do some analysis (logistic regression) on the retrieved data (the 30 sampled rows) and then use the results to see if it can predict the rest of the data (the remained 10 rows). Therefore, one random sample is not enough. I want this procedure to be repeated many times. What is the best approach? Can this be done in excel or is it better to do some programming (which I preferably avoid)? Many thanks in advance /Nina |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying random rows in excel to another sheet
Hi,
Just add a column with function =rand(), sort it on that column and pick-up the first rows you need.. The sorting will always be random as the rand function output will change at each sorting. Regards Jean-Yves "Neda-k" wrote in message ... Hello, I have two issues 1) I have a sheet consisting of 40 rows and 9 columns. I want to randomly pick/cut 30 of these rows (not columns, because each column in each row is dependent to each other, therefore I need the randomly picked row to contain all its related columns) and paste them to a new sheet. I would also like to cut and paste the remaining 10 row to another sheet. I have tried the rand function but it only randomly picks a column. 2) I would also like to get some advice on approach. The reason for the randomising is that I need to do some analysis (logistic regression) on the retrieved data (the 30 sampled rows) and then use the results to see if it can predict the rest of the data (the remained 10 rows). Therefore, one random sample is not enough. I want this procedure to be repeated many times. What is the best approach? Can this be done in excel or is it better to do some programming (which I preferably avoid)? Many thanks in advance /Nina |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying random rows in excel to another sheet
Hi,
If I understand correctly then you should be ablue to do it like this. First in a convenient place on your sheet refer to your data table using the indirect function =INDIRECT("A"&ROW(A2)) =INDIRECT("B"&ROW(A2)) etc =INDIRECT("A"&ROW(A3)) Etc =INDIRECT("A"&ROW(A30)) Do this until you build your 30 row by 9 column data table for analysis and repeat this to build a second 10 row by 9 column table Then put this in a cell next to your data =Rand() and drag down for the 40 rows Every time you tap F9 you will get a different and random selection of rows in your 2 indirect tables. Mike "Neda-k" wrote: Hello, I have two issues 1) I have a sheet consisting of 40 rows and 9 columns. I want to randomly pick/cut 30 of these rows (not columns, because each column in each row is dependent to each other, therefore I need the randomly picked row to contain all its related columns) and paste them to a new sheet. I would also like to cut and paste the remaining 10 row to another sheet. I have tried the rand function but it only randomly picks a column. 2) I would also like to get some advice on approach. The reason for the randomising is that I need to do some analysis (logistic regression) on the retrieved data (the 30 sampled rows) and then use the results to see if it can predict the rest of the data (the remained 10 rows). Therefore, one random sample is not enough. I want this procedure to be repeated many times. What is the best approach? Can this be done in excel or is it better to do some programming (which I preferably avoid)? Many thanks in advance /Nina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a new sheet containing random sampler 200 rows over 2000 | Excel Worksheet Functions | |||
Copying certain rows to another sheet | Excel Programming | |||
Copying rows from one sheet to another.... | Excel Worksheet Functions | |||
Copying random rows to other worksheets | Excel Programming | |||
Copying rows to a new sheet | Excel Programming |