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.

You may want to visit J.E. McGimpsey's site and grab a copy of his =RandInt()
function.
http://www.mcgimpsey.com/excel/udfs/randint.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

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.

--

Dave Peterson

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.

"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.

using the formula =RANDBETWEEN(1,15) to
generate random numbers in cells A2 through A6.

One way...

This formula references cell A1. A1 must not contain a number from 1 to 15.

Create this named formula:
Goto the menu InsertNameDefine
Name: Nums
Refers to: ={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}
OK

Enter this array formula** in A2 and copy down to A6:

=SMALL(IF(ISNA(MATCH(Nums,$A$1:A1,0)),Nums),INT(RA ND()*(15-ROW()+ROW(A$2)))+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


"Paul" wrote in message
...
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.

Paul, watch this 8 min video on random sampling.

http://www.youtube.com/watch?v=SoK9kq-0uXg

If this post helps click Yes
---------------
Jacob Skaria


"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.

That dude sounds like he's half asleep!

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Paul, watch this 8 min video on random sampling.

http://www.youtube.com/watch?v=SoK9kq-0uXg

If this post helps click Yes
---------------
Jacob Skaria


"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.

Yes...as if not interested...

"T. Valko" wrote:

That dude sounds like he's half asleep!

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Paul, watch this 8 min video on random sampling.

http://www.youtube.com/watch?v=SoK9kq-0uXg

If this post helps click Yes
---------------
Jacob Skaria


"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.

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.

"B. R.Ramachandran" wrote:
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.

Yes, I do the same for a quick-and-dirty implementation. The problem is:
it is difficult to generate a new set of random numbers quickly.

I prefer to put the =RAND() in some out-of-the-way column, and
copy-and-paste-special-value into the range depended on, B1:B15 in your
case.

Of course, the RAND column keeps generating new random values unnecessarily.
A small performance hit in this case. If it proves costly in larger
problems, obviously is it better to avoid them, either by pasting values
over as you suggest or by using a non-volatile macro.


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

"B. R.Ramachandran" wrote in
message ...
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.