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


--
I keep hitting "Esc" -- but I'm still here!

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

--
Kind regards,

Niek Otten

"plh" wrote in message
...
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


--
I keep hitting "Esc" -- but I'm still here!

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

=RAND()

copy down to B100, select both columns and do datasort and sort by column
B, delete column B


--

Regards,

Peo Sjoblom

"plh" wrote in message
...
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


--
I keep hitting "Esc" -- but I'm still here!

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"plh" wrote in message
...
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


--
I keep hitting "Esc" -- but I'm still here!

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))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

In article , Max says...

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"plh" wrote in message
...
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


--
I keep hitting "Esc" -- but I'm still here!




--
I keep hitting "Esc" -- but I'm still here!

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"plh" wrote in message
...
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