I totally agree with you! Also, thanks for the =myRand() UDF.
I wasn't sure whether Paul wants a static set or a volatile regenerating set
of non-repeating random numbers in A2-A6. Since =RANDBETWEEN() is also
volatile, I thought the approach is adequate.

If he wants a static set, he could still follow the approach and do a "copy"
on A2-A6 followed by "paste special'-- "values" on the same location.

Kind regards,
Ramachandran

"JoeU2004" wrote:

"B. R.Ramachandran" wrote:
Every time you hit F9 button, you will have a new set of non-repeating
random numbers between 1 and15 in A2-A6.

.... And every time you edit any cell in the workbook :-(.

Presumably that's okay with Paul, since the same is true about RANDBETWEEN.

But one way to avoid that "volatile" behavior is to replace =RAND() with
=myRand(), which is a UDF defined as follows:

Function myRand(Optional arg as Range) as Double
Static first as Integer
If first = 0 Then Randomize: first = 1
myRand = Rnd()
End Function

If you pass a cell or cell range to myRand, it will recalculate whenever any
cell in the range is edited. Otherwise, myRand is recalculated only when
the worksheet is recalculated, e.g. ctrl+alt+F9.


----- original message -----

"B. R.Ramachandran" wrote in
message ...
Hi,

In a helper column, say B1 to B15, enter the formula =RAND(). This will
create 15 random numbers.

Now, in A2, enter the formula, =RANK(B1,$B$1:$B$15,1)
and drag (autofill) the formula down to A6.

Every time you hit F9 button, you will have a new set of non-repeating
random numbers between 1 and15 in A2-A6.

Regards,
B. R. Ramachandran




"Paul" wrote:

I'm using the formula =RANDBETWEEN(1,15)
to generate random numbers in cells A2 through A6.
I don't want any of those random numbers to be repeated.
Any suggestions on how to do this?
Thanks for your help.