Can I randomise the order of a number of rows?

[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

 

[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.

 

[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

 

[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

 

[Max]

Oops, pasted the formula from the wrong cell ..

Line:

Put in C1: =INDEX(A:A,RANK(B73,$B$1:$B$100))

should be:
Put in C1:=INDEX(A:A,RANK(B1,$B$1:$B$100))

 

[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

 

[Max]

You're welcome !
Thanks for the feedback ..