One way...

This requires that there be a cell before the first random number cell and
that cell must not contain one of the random numbers.

So, let's assume you want the 6 random non-repeating numbers from 1 to 49 in
the range B2:G2. Cell A2 must not contain a number from 1 to 49.

Enter this array formula** in B2 and copy across to G2:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:49")),$A2:A2, 0)),ROW(INDIRECT("1:49"))),INT(RAND()*(49-(COLUMNS($A2:B2)-1)))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"mike_vr" wrote in message
...
Hi there

Does anyone know how I can have six cells next to each other, using the
=RANDBETWEEN(1,49) function, where none of the cells equal each other?
I.e. a random lottery function without having two of the same numbers.
I'm trying to do this without creating a huge nested If statement so any
help would be appreciated.

Cheers
Mike