Generate & store distinct random numbers

[puffthemagicdragon]

I need to add a facility to my application which will generate random numbers which I can then append to a table. The output must be five numbers between 1 & 59 and each must be different (i.e. must not repeat a number in a line).

First: Int((59-1+1)*Rnd()+1) produces the right number range, but after (literally) days of trying I can't find a way to make sure each one is distinct from the others in each line.

I suspect the answer might be to write each number into an array and then loop back to check whether the next is different, but am unsure how to code this, much less how the get the result out of the array

Even a tiny bit of help in the right direction will be much appreciated!

 

[Rabbie]

Surely Int(59-1+1) is the same as Int(59) is the same as 59.??

 

[puffthemagicdragon]

It doesn't make a lot of sense to me either, but that's the MS code and it does work. Unfortunately the question of how you get five different numbers in a sequence remains

 

[Rabbie]

If you are only looking 5 different numbers then you can five different identifiers to hold the numbers and check after you generate numbers 2,3,4 and 5 that the number has not already come out.

 

[puffthemagicdragon]

I tried doing this with a nested query, but it doesn't work, presumably because the random number is generated dynamically, rather than being a constant. This is why I thought temporarily dropping the numbers into an array might help, but I'm not sure how to code this...

 

[gemma-the-husky]

this doesnt look right

Int((59+1-1)*Rnd()+1)

as rabbie points out you get exactly the same with
Int((59)*Rnd()+1)

------
lets break it down
rnd() produces a random number less than 1
so 59 * this number gives a number in the range

0<x<59

ie definitely greater than 0, and definitely less than 59

next - the int function drops the remainder, so now we have a number in the range 0 to 58

so add 1 and we get a number in the range 1 to 59.

so

Int((59)*Rnd()+1) it is.

Now -
a) you need to think about the rng SEED, otherwise you will get the same numbers each time

b) yes - you may get duplicates - thats what random is

ie pick a number
58/59 the next one is different
57/59 the next one is different
56/59 the next one is different
55/59 the next one is different

so only 84% chance they are different (1 * 58/59 * 57/59 * 56/59 * 55/59)
and about 16% you do get a clash. (surprisingly high isnt it - thats probability for you)

you just have to test each number, and reject it if its a duplicate

 

[Banana]

Aircode:

Public Function RndArray() As Variant

Dim i(4) As Integer
Dim j As Integer
Dim k As Integer

Do Until j = 5
   i(j) = Int((59)*Rnd()+1)
   For k = j to 0 Step -1
     If Not i(j) = i(k) Then 
       j = j + 1
     End If
  Next
Loop

RndArray = i()

End Function

 

[raskew]

Hi -

Here's another created to generate lottery numbers.

Public Function RandLotto2(Bottom As Integer, Top As Integer, _
                          Amount As Integer) As String
                    
'*******************************************
'Purpose:   Produce x random/unique/sorted numbers
'           between bottom and top.
'Coded by:  raskew
'Sources:   http://www.ozgrid.com/VBA/RandomNumbers.htm
'           http://www.tek-tips.com/viewthread.cfm?qid=756905 (sort routine - Roy Vidar's post)
'Inputs:    ? RandLotto2(1, 55, 5)
'Output:    1 5 11 18 44  (5 unique, random, sorted
'           numbers between 1 and 55)
'*******************************************

    Dim iArr As Variant
    Dim i As Integer
    Dim j As Integer
    Dim r As Integer
    Dim Temp As Integer
    
    ReDim iArr(Bottom To Top)
    For i = Bottom To Top
        iArr(i) = i
    Next i
    
    For i = Top To Bottom + 1 Step -1
        Randomize
        r = Int(Rnd() * (i - Bottom + 1)) + Bottom
        Temp = iArr(r)
        iArr(r) = iArr(i)
        iArr(i) = Temp
    Next i
    
    For i = Bottom To Amount
        For j = i + 1 To Amount
            If iArr(i) > iArr(j) Then
                Temp = iArr(i)
                iArr(i) = iArr(j)
                iArr(j) = Temp
            End If
        Next j
    Next i
    
    For i = Bottom To Bottom + Amount - 1
        RandLotto2 = RandLotto2 & " " & iArr(i)
    Next i
    
    RandLotto2 = Trim(RandLotto2)
    
End Function

'Note: An example of this function called 10 time in succession:
' 9 20 30 43 46
' 1 10 16 38 48
' 4 28 43 44 46
' 10 31 44 47 48
' 1 9 15 44 45
' 2 4 16 49 54
' 6 17 40 46 55
' 8 17 33 51 55
' 1 20 23 45 48
' 2 12 28 33 49


Bob

 

[puffthemagicdragon]

Thank you, I wouldn't have spotted this issue; always a problem when you don't really know what you are doing

this doesnt look right

Int((59+1-1)*Rnd()+1)

as rabbie points out you get exactly the same with
Int((59)*Rnd()+1)

------
lets break it down
rnd() produces a random number less than 1
so 59 * this number gives a number in the range

0<x<59

ie definitely greater than 0, and definitely less than 59

next - the int function drops the remainder, so now we have a number in the range 0 to 58

so add 1 and we get a number in the range 1 to 59.

so

Int((59)*Rnd()+1) it is.

Now -
a) you need to think about the rng SEED, otherwise you will get the same numbers each time

b) yes - you may get duplicates - thats what random is

ie pick a number
58/59 the next one is different
57/59 the next one is different
56/59 the next one is different
55/59 the next one is different

so only 84% chance they are different (1 * 58/59 * 57/59 * 56/59 * 55/59)
and about 16% you do get a clash. (surprisingly high isnt it - thats probability for you)

you just have to test each number, and reject it if its a duplicate

 

[raskew]

...but after (literally) days of trying I can't find a way to make sure each one is distinct from the others in each line.

Give Post #8 a try, modifying it to meet your needs.

Bob

 

[boblarson]

Give Post #8 a try, modifying it to meet your needs.

Bob

Great function Bob! I like it (and have saved it for future use )

 

[raskew]

Thanks Bob Larson, but I don't take credit for the engineering that went into it.
(But it seems to work without fault.)

Take a look at the 'Sources:' I've listed. That's where the brilliance resides.

I could be guilty of 'copywright infringement' here since I've basically copied
the Excel UDF with only minor modifications.

Best wishes - (other) Bob

P.S. Would you like to buy a lottery ticket? I can help you.

 

[puffthemagicdragon]

Thank you Bob, I will see if I can bend my somewhat puny mind to adapt this. Your assistance is very much appreciated.

Give Post #8 a try, modifying it to meet your needs.

Bob