<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="]<o></o>[/FONT]