Can I randomise the order of a number of rows?

P

plh

Hello Excel Gurus,
I have a spreadsheet containing a column of 100 items. I want to change from the
original ordered state to a randomized state. One way would be to create a
randomized column of numbers from 1 to 100 in an adjacent column, such that no
number is repeated, then order the two columns by that list. Is there a way to
do that? If I use RANDBETWEEN(1,100)I am certain to have a lot of numbers
repeated. Is there a way around that?
Thank You,
-plh
 
N

Niek Otten

What's the problem with numbers being repeated? They are still random,
aren't they?
You won't get the same order next time.
 
P

Peo Sjoblom

Assume your values are in A1:A100, in B1 put

=RAND()

copy down to B100, select both columns and do data>sort and sort by column
B, delete column B
 
M

Max

Another play ..
Assuming items to be shuffled in A1:A100
Put in B1: =RAND()
Put in C1: =INDEX(A:A,RANK(B73,$B$1:$B$100))
Select B1:C1, copy down to C100
Col C returns a random shuffle of items in col A
Just press F9 to generate a new shuffle
 
P

plh

Hello Max,
Thank you, that works swimmingly!
I figured "B73" was an error, translated the whole thing to the correct position
on the form, and ended with: =INDEX($C$14:$C$113,RANK($E14,$E$14:$E$113))
-plh
 
Top