Haw to Randomized available data

A

Adda

I have 10 columns of data. I wanted to randomized each column so that I will
get a new combination of data. Its like permutation but actually applying it
to the data. is there any function I can use for this?
 
F

Fredrik Wahlgren

Adda said:
I have 10 columns of data. I wanted to randomized each column so that I will
get a new combination of data. Its like permutation but actually applying it
to the data. is there any function I can use for this?
Click to expand...

Assuming your data is in colums A to K, do this

1) Select column B and then select Insert Columns
3) Keep on inserting colums so that every other column is empty
4) Insert =RAND() in the empty columns and dragfill downwards
At this point, you should turn on the recorder if you want to do this random
sort every now and then

5) Select columns A and B
6) Select Data|Sort. Sort on column B
7) Repeat until you have sorted the last column on the column with the RAND
function
8) Hide the colums with the rand formula
9) Turn off the recorder

I think you could get away with two colums with the RAND function. It
apppears as if Data|Sort has a limit on how many columns it can sort. You
would have to do the sort several times and change the selected columns each
time. Either way, you have to sort 10 times.

/Fredrik
 
R

Ragdyer

An often used procedure for something like this is to add a column
containing the RAND() function, and then sort the columns, using the returns
from the RAND() function as the sort keys.

With 10 columns however, this procedure would not "mix up" the relationships
between the rows within these columns. This may or may not be acceptable.

In an adjoining column, or an inserted column, enter,
=RAND()
and copy down as needed.
Now, simply select all the columns and:
<Data> <Sort>
And choose the Rand column as the "Sort By".

Another approach, where sorting the original data is *not* necessary, is to
use a formula which will *display* the original data in a random order,
matching, once again, an out-of-the-way column containing the Rand()
function.
Here however, you could in reality, create 10 columns containing the Rand()
function, and then have 10 columns of formulas, each accessing a different
Rand column, therefore displaying a truly "mixed up", randomized display of
the original 10 columns of data.
A new random display would be produced with each hit of the <F9> (calculate)
key.

With original data in A1:J100

In AA1, enter =RAND(),
And drag across and then down to create 10 columns, AA1:AJ100

Then, let"s say we enter this formula in L1:

=INDEX(A$1:A$100,RANK(AA1,AA$1:AA$100))

Drag across to copy to U1,
Then drag L1:U1 down to copy to Row100.

You now have L1 to U100 displaying a random order of your original data,
where you can now hit <F9>, and get a new random display.
 
Top