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?


Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...