copying random rows in excel to another sheet

[Neda-k]

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

 

[Jean-Yves TFELT]

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

 

[Mike H]

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