How do I set up a random Colum 1-55 without repeating a number

[Max]

From your subject line:
Re: How do I set up a random Colum 1-55 without repeating a number
(Note that you should always write the full query within the message area)

One interp & way to set it up ..

Put in
A1: =ROW(A1)
B1: =INDEX(A:A,RANK(C1,$C$1:$C$55))
C1: =RAND()

Select A1:C1, copy down to C55

B1:B55 will return a random shuffle of the numbers 1 - 55 from col A
(Replace the formula in A1:A55 with whatever 55 items that's to be shuffled)

Press F9 to regenerate afresh

 

[B.J]

Hi Max
What I want is to show 1-55 in a colum and when i hit F9 the whole colum
changes.
Brian

 

[Max]

.. What I want is to show 1-55 in a colum
and when i hit F9 the whole colum changes.

But isn't that what col B (B1:B55)
in the suggested set-up delivers ?

(You could always hide away cols A & C)

 

[B.J]

No B shows individual numbers also it repeats numbers befor the whole list is
shown.

 

[Max]

No B shows individual numbers
also it repeats numbers befor the whole list is shown.

Guess I'm not sure what happened over there <g>

Here's a sample implementation to play with:
http://cjoint.com/?lohRA4AVqG
Set_up_a_random_col_1-55_without_repeating_a_number_BJ_misc.xls

The randomized numbers 1 - 55 within B1:B55 **do not repeat**

**Prove this easily for yourself by copying B1:B55, then paste special as
"values" in another col, and then sort that col in ascending order.

Pressing F9 will produce another random shuffle of the numbers

 

[B.J]

Thanks max a great help
Many Thanks
Brian

Guess I'm not sure what happened over there <g>

Here's a sample implementation to play with:
http://cjoint.com/?lohRA4AVqG
Set_up_a_random_col_1-55_without_repeating_a_number_BJ_misc.xls

The randomized numbers 1 - 55 within B1:B55 **do not repeat**

**Prove this easily for yourself by copying B1:B55, then paste special as
"values" in another col, and then sort that col in ascending order.

Pressing F9 will produce another random shuffle of the numbers

 

[Max]

You're welcome, Brian !