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


New Member
Aug 3, 2010
<o:p> </o:p>
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:p></o:p>
2 000 000 tickets are sold at 2.5€ <o:p></o:p>
1 of them is worth 50 000€<o:p></o:p>
10 of them 2500€<o:p></o:p>
400 of them 250€<o:p></o:p>
3000 of them 25€<o:p></o:p>
550 000 of them €5<o:p></o:p>
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:p> </o:p>
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:p></o:p>
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:p> </o:p>
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.
    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
                '...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?


Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

West Man

Well-known Member
Mar 27, 2006
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.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back