Random Number Generation

[rich987652]

I am trying to devise a simple spreadsheet to pick some employees who should take a random drugs and alcohol test.

I have been using the RANDBETWEEN function which works fine with one exception. If for instance, I ask for 10 numbers to be generated using the RANDBETWEEN function, then often one number gets selected more than once leaving me with less than the required number of people.

I have used an array formula to identify cells that have been replicated, so that I can leave an error message which tells the user to press F9 to regenerate the numbers.

But this is awkward, is there any way I can prevent one number being generated more than once ??

Hope I have explained myself adequately.

Richard

[Paul B]

Have a look at http://www.mrexcel.com/board/viewtop...c=8324&forum=2

[Nimrod]

This will generate a set of 5 random numbers ... to make a longer set just specify a different column letter in the Range setting i.e. change ("B1:F" to ("B1:H" for a set of 10 numbers.

Public Sub RandNum7()
    Randomize
    Cells.Clear
    For Each c In Range("B1:F" & (InputBox("how many sets?")))
TryAgain:
            c.Value = (Int((99 * Rnd) + 1))
            If Not Range(Cells(c.Row, 1), Cells(c.Row, (c.Column - 1))).Find(c.Value, LookIn:=xlValues) Is Nothing Then GoTo TryAgain
        Next c
End Sub

[ This Message was edited by: Nimrod on 2002-05-15 14:51 ]

[Nimrod]

This one will prompt you for how many random numbers to generate and then generate (n)uniuque numbers ... cheers

Public Sub RandNum9()
    'the row of random numbers are unique within the row
    Randomize
    Cells.Clear
    For Each c In Range(Cells(1, 2), Cells(1, 1 + (Val(InputBox("how many numbers?")))))
TryAgain:
            c.Value = (Int((99 * Rnd) + 1))
            If Not Range(Cells(c.Row, 1), Cells(c.Row, (c.Column - 1))).Find(c.Value, LookIn:=xlValues) Is Nothing Then GoTo TryAgain
        Next c
End Sub

To change the number range modify((99 * Rnd) + 1)) this range is from 1 to 99

Just cut and paste this into your workbook module and your ready to rock-n-roll

OR modify program so it asks for your range:

Public Sub RandNum10()
' Generate one row of variable length
    Randomize
    Cells.Clear
    
    LowNum = (Val(InputBox("Lowest # ?")))
    HighNum = (Val(InputBox("Highest # ?")))
    
    For Each c In Range(Cells(1, 2), Cells(1, 1 + (Val(InputBox("how many numbers?")))))
TryAgain:
            c.Value = (Int((HighNum * Rnd) + LowNum))
            If Not Range(Cells(c.Row, 1), Cells(c.Row, (c.Column - 1))).Find(c.Value, LookIn:=xlValues) Is Nothing Then GoTo TryAgain
        Next c
End Sub

[ This Message was edited by: nimrod on 2002-05-15 16:04 ]

[Mark O'Brien]

..or you could just make sure that any suspected junkies or alcoholics are assigned that number.

[Nimrod]

Yea but Mark what company can survive without an IT dept?

[Chris Davison]

[Mark W.]

See my posting at http://www.mrexcel.com/board/viewtop...c=8146&forum=2...

Or...

If you'd like to choose a random sample from a list of names apply an Advanced AutoFilter with a computed criteria using the formula...

=RAND()<=0.025

...to pull a random, percentage-based sample of your list (in the case above -- 2.5% of the names listed).

For more on this approach see the Excel Help topic for "Examples of advanced filter criteria" and take note of the paragraph titled, "Conditions created as the result of a formula".

[ This Message was edited by: Mark W. on 2002-05-15 16:39 ]

[Nimrod]

-Studies show the people that consume large quantities of drugs and alcohol are less ambitious , low self-esteem and more likely to be divorced.

-Other studies show that copious corporate expenditures occur from : ambitious employees leaving the company for promotions, employees unwilling to work late due to home-life, and employees thinking their above corporate directives (i.e. high self-esteem !

Question: Shouldn’t corporations be encouraging consumption of drugs and alcohol ?


[ This Message was edited by: Nimrod on 2002-05-15 16:46 ]

[verluc]

On 2002-05-15 14:51, Nimrod wrote:
This will generate a set of 5 random numbers ... to make a longer set just specify a different column letter in the Range setting i.e. change ("B1:F" to ("B1:H" for a set of 10 numbers.

Public Sub RandNum7()
    Randomize
    Cells.Clear
    For Each c In Range("B1:F" & (InputBox("how many sets?")))
TryAgain:
            c.Value = (Int((99 * Rnd) + 1))
            If Not Range(Cells(c.Row, 1), Cells(c.Row, (c.Column - 1))).Find(c.Value, LookIn:=xlValues) Is Nothing Then GoTo TryAgain
        Next c
End Sub

[ This Message was edited by: Nimrod on 2002-05-15 14:51 ]

Hi, your macro is very useful for me.Is it possible to change your macro in the way that the difference between the numbers in a row is not more then 15
Many thanks for help