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

#### EvertTr

##### New Member
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.

Code:
``````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]

### 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
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.

1,106,589
Messages
5,512,238
Members
408,885
Latest member
binduchekuri