permute contents of a range

[Guest]

hi guys

i have a range of 16 c ells, say a1-a16 and they contain numbers 1 to 16, in
this order. i want to creat several ranges b1-b16, c1-c16 etc. that would
contain the same numbers but in a random order.
how can i do this?
thank you dragos

 

[Domenic]

Hi,

B1, copied across and down:

=RANDBETWEEN(MIN($A$1:$A$16),MAX($A$1:$A$16))

You'll need to make sure that you have the Analysis ToolPak add-in
installed.

Hope this helps!

 

[Ragdyer]

This will give you *true* random numbers.
What that means is, that you will get duplicates.

Enter this in B1, and drag down to B16 to copy:

=INT(RAND()*(16-1)+1)

 

[Ragdyer]

If this works for you, then all you have to do to change the numbers is to
hit <F9> to get a new permutation.

 

[Ragdyer]

In re-reading your post, I really think you're looking to just re-arrange 1
to 16 in a random *order*, and you're not looking for random *numbers*
between 1 and 16.

If that be the case, you could try this:

Enter 1 to 16 down column A.
In B1, enter this:

=RAND()

And copy down to B16.

In C1, enter this:

=INDEX(A$1:A$16,MATCH(LARGE(B$1:B$16,ROW()),B$1:B$16,0))

And copy down to C16.

This will give you the numbers 1 to 16 in random *order*, without
duplicates.
Again, to renew (change) the order of numbers, just hit <F9>.

 

[Guest]

Hi Ragdyer

Yes, it works the way you showed, thanks a lot.
What I am amazed about is that such a simple need is covered with such a
complicated formula. I would expect Excel to have a simple formula for this
type of operation that I needed to do. Sth. for mr. Gates to think about for
the future.

But again thank you for you excellent and prompt answer
dragos

 

[Ragdyer]

Thanks for the feed-back.

BTW
You could accomplish this random order sequencing *without* using any
formulas at all.

After you've copied the RAND() down column B, simply select *both* column A
and B (A1:B16), and do:

<Data> <Sort>
Choose sort by column B, then <OK>.

And you have your random order without using a formula.
Merely repeat the sort each time you wish to obtain a new order sequence.