Can I randomise the order of a number of rows?

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" <[email protected]> wrote in message
news:[email protected]...
> 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 data>sort and sort by column
B, delete column B


--

Regards,

Peo Sjoblom

"plh" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <#[email protected]>, 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" <[email protected]> wrote in message
>news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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