Random number sequence

[tghcogo]

I want to fill a column with the numbers 1 to 20, in a random sequence with no repeats, any ideas?

[daddylonglegs]

You could try this

Put this formula in B1 and copy down to B20

=RAND()

in A1 copied down to A20

=RANK(B1,B$1:B$20)

A1:A20 now contains your randomly arranged 1-20. Hide column B if you wish. Random numbers will change whenever worksheet is re-calculated (hit F9 to initiate that)

[tghcogo]

Thanks daddylonglegs, just the job, I didn't think of ranking them.

[vane0326]

Input formula in cell A2 and copy down. This formula will not generate any repeats.


=SMALL(IF(COUNTIF($A$1:A1,ROW($1:$99))<>1,ROW($1:$99)),1+INT(RAND()*(99-ROW()+ROW($A$2))))

The formula is an-array must hold down:

Ctrl,Shift,Enter