# 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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.

Replies
1
Views
11K
Replies
12
Views
1K
Replies
30
Views
4K
Replies
0
Views
431
Replies
3
Views
2K

Threads
1,220,009
Messages
6,151,441
Members
451,028
Latest member
greekness1

### 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 MrExcel.com.
Allow Ads at MrExcel

### 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 "mrexcel.com".
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