Impossible? Iterating the creation of 2 million unique random variables.

[EvertTr]

Greetings,<o></o>
<o> </o>
The following problem is a real-life problem (part of internship). I will credit your help & will prove it. If you are interested in the story:<o></o>
2 000 000 tickets are sold at 2.5€ <o></o>
1 of them is worth 50 000€<o></o>
10 of them 2500€<o></o>
400 of them 250€<o></o>
3000 of them 25€<o></o>
550 000 of them €5<o></o>
The remaining 1446589 are worth nothing.
(No worries, it is not a casino, all profits go to countless organizations who can use the money, f.e. amateur film producers etc)
<o></o>
<o> </o>
To give statistical information to the company, for instance in a situation where the commpany already sold 1500 000 tickets, I need to run a simulation in Excel (Monte Carlo). <o></o>
F.e.: What are the odds that selling 1500 000 tickets results in the company paying out 1800 000 €
So I need distributional information
<o></o>
<o> </o>
Straight mathematics won't do the trick. (At least I didn't obtain any results)


In order to obtain distributional information, I need to generate non-recurring (unique) random numbers. I found some code which uses a cell per number.

Sub Macro1()
    'Macro written by Trebor76, Jun 2 2010 to extract a range of unique numbers.

      Dim lngNumberFrom As Long, _
        lngNumberTo As Long, _
        lngRandomNumber As Long, _
        lngCellExtract As Long, _
        lngRowNumber As Long
    
    Application.ScreenUpdating = False

      lngNumberFrom = 1 'Minimum number in range
    lngNumberTo = Range("A2").Value 'Maximum number in range
    lngNumberExtract = Range("A1").Value 'Amount of unique numbers to extract
    lngRowNumber = 1 'Initial row extract number
    
    'Clear any existing entries in Column B.
    Columns("B").ClearContents
    
    For lngRandomNumber = 1 To lngNumberExtract
        lngCellExtract = Int((lngNumberTo + 1 - lngNumberFrom) * Rnd + lngNumberFrom)
            'If the random number generated is unique, then...
            If Evaluate("COUNTIF(B1:B" & lngNumberExtract & "," & lngCellExtract & ")") = 0 Then
                '...put it into the next available row in Column B.
                Range("B" & lngRowNumber).Value = lngCellExtract
                lngRowNumber = lngRowNumber + 1
            'Else...
            Else
                '...ignore that number and try again.
                lngRandomNumber = lngRandomNumber - 1
            End If
    Next lngRandomNumber
    
    Application.ScreenUpdating = True
    
End Sub

The problem is: I need to get unique random numbers in a magnitude of millions (in this case 2000 000 or less)
The problem is that this code won't go fast enough. Another problem may be: where to put all of these numbers? Because Monte Carlo means that I need to rerun the picking of a million variables a large number of times



If this would be possible, all I need is a little bit of code that performs a kind of "countif"
The first 1446589 numbers would be valueless
The following 550 000 numbers would be worth 5€
...

So for each time we generated the random variables, we know how much the company needs to pay

Somehow I believe this is impossible?





[FONT=&quot]<o></o>[/FONT]

 

[West Man]

I can not help with the VBA, but perhaps can suggest a method to simplify the problem. With 550,000 low value tickets out of a total of 2,000,000, the result of a series of simulations would likely be very close to the probability of one these tickets winning, that being 550,000/2,000,000 for each of the 550,000 tickets sold. So you would have on average 151,250 of these winning. You could then reduce the number of tickes available by this amount and do a simulation on the remainder of the prizes, greatly reducing the amount of random numbers needed.