Generate Random Numbers with no duplicates

[jmcmahon]

I need to generate 5 random numbers
from 1 to 15 in cells a3 to a7 with
no duplicates.

I currently use the code =RANDBETWEEN(1,15)
in each cell which works great, but it can generate a duplicate number.

Is there code I can add to =RANDBETWEEN(1,15)
That would not create a duplicate number
in cells a3 to a7?
Or possible VB code to do the same?

Thanks

 

[Yogi Anand]

Hi jmcmahon:

One way would be to use the MRAND function as ...

=MRAND(5,,15)

MRAND is part of the MoreFunc Add-In available from http://longre.free.fr/english

Book2
	A	B	C	D
1
2
3	13
4	8
5	7
6	5
7	14
8
Sheet1

</SPAN>

 

[jmcmahon]

I have Excel 2000, I can't seem to get the
morefunc addin installed.

I've tried everything I can think of to get
it installed and my addins won't recognize it.

 

[Aladin Akyurek]

On 2003-02-15 16:31, jmcmahon wrote:
I have Excel 2000, I can't seem to get the
morefunc addin installed.

I've tried everything I can think of to get
it installed and my addins won't recognize it.

Unzip morefunc.zip in a New Folder on your destop.
Open the folder C:Program FilesMicrosoft OfficeOfficeLibrary in Windows Explorer or via My Computer.
Drag and Drop the following files from New Folder into ...Library

MOREFUNC.CNT
Morefunc.GID
MOREFUNC.HLP
Morefunc.xll

Start Excel
Activate morefunc via Tools|Add-Ins.

 

[jmcmahon]

I've tried that again and it still doesn't work. When I type in {=MRAND(5,,15)}
in the formula bar it just gives me ###
or just show the formula in the cell.

At this point i'd settle for a vb solution
instead of an addin.

I click on tools then addins then I click
on the box for morefunc (add in functions)
Then I click on OK then I get nothing.

When the other add-ins are highlighted, there is a description at the bottom of the add-in, when morefunc is highlighted there is no description at the bottom.

Could that be a problem?

 

[Aladin Akyurek]

On 2003-02-15 17:11, jmcmahon wrote:
I've tried that again and it still doesn't work. When I type in {=MRAND(5,,15)}
in the formula bar it just gives me ###
or just show the formula in the cell.

At this point i'd settle for a vb solution
instead of an addin.

I click on tools then addins then I click
on the box for morefunc (add in functions)
Then I click on OK then I get nothing.

When the other add-ins are highlighted, there is a description at the bottom of the add-in, when morefunc is highlighted there is no description at the bottom.

Could that be a problem?

Enter in A1...

=EVAL(1+5)

What result do you get?

 

[Yogi Anand]

Hi jmcmahon:

If you have successfully installed the MoreFunc, you enter the following formula ...

=MRAND(5,,15)

This is an array formula, and is to be entered with CTRL+SHIFT+ENTER rather than with simply ENTER. The curly brackets are not to be entered manually, those are entered by the system, when the formula is entered as an array formula.

I hope it makes sense. If you need to discuss this further, please post back and let us take it from there.

 

[Nimrod]

Hello Jm:
If your having trouble with a formula soluton here's a VBA solution for you:

Public Sub RandNum()
Randomize
Cells.Clear
For Each c In Range("A3:A7")
TryAgain: c.Value = (Int((15 * Rnd) + 1))
If Not Range("A2:A" & c.Row - 1).Find(c.Value, LookIn:=xlValues) Is Nothing Then GoTo TryAgain
Next c
End Sub

just paste this in your VBA window and your done.

 

[jmcmahon]

Thanks for the help I appreciate it.

John