Need formula for Random ##'s within range AND Relative to each other 3

[kwbMitel]

I'm creating a spreadsheet to generate Lottery Ticket numbers. Why? To settle a bet.

Randbetween(1,49) is great for 1 number but I need 6 NON-Matching numbers withing the range.

I've gotten around this for now by generating 12 numbers and using if then statements to find the first 6 unique numbers but I don't think this method will satisfy my sceptics.

Is there a simple formula to generate 6 distinct, non-matching numbers within a range? (1 - 49)

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[kjv1611]

Does it have to be in an Excel formula, or can you use other options, like say a custom VBA function?

 

[Gavona]

I would enter your 49 possible values into column A.
Enter the formula = Rand() beside each in column B
Data sort by column A
Each time you sort you get a new order (the sort triggers recalculation of the Rand() function)
You select the first x numbers

Gavin

 

[kwbMitel]

Gavona - Need Billions of tickets - Already using a generate 12 random pick 6 unique method regardless.

kjv611 - I'm a VBA novice. I can record and edit but I can't get too creative. The method I would use would again require if then statements until 6 unique numbers are generated. The problem being I need to convince someone of a probabilty statement and the method needs to be foolproof. They would understand my code much less than my current Generate 12 and pick 6 method.

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[mintjulep]

Interesting solution here:

http://www.get-digital-help.com/2009/07/03/how-to-create-a-random-list-of-unique-numbers-in-excel/

 

[Gavona]

Billions is too many for Excel to handle.
A macro could operate the sort and then write the results from the top six into a file (or another sheet if within Excel's row limit and memory caspacity) then loop back to the beginning.



Gavin

 

[Gavona]

Billions is too many for Excel to handle.
A macro could operate the sort and then write the results from the top six into a file (or another sheet if within Excel's row limit and memory capacity) then loop back to the beginning.



Gavin

 

[kwbMitel]

Gavona: Billions total not all in one go.

I typically loop 10000 at a time and store the results in concecutive cells on another sheet.

My current method generates 1,000,000,000 in about 50 minutes (speed is not my goal here, only simplicity)

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[kwbMitel]

Mintjulep: that looks exactly like what I'm looking for but It does not work as directed.

How do I create a random list of unique numbers from say 1 to 10, without using VBA (Generate unique random numbers) (Unique random numbers in VBA)
or without enabling iterative calculation in excel options
or not using "helper" columns?

Directions:

Copy formula into cell A2

=INDEX(SMALL(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, ROW($1:$10), ""), ROW(INDIRECT("1:"&SUM(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, 1, 0))))), ROUND(RAND()*SUM(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, 1, 0)), 0), )+ CTRL + SHIFT + ENTER copied down to cell A14.

Results should show 10 different numbers in cells A2 to A11 followed by errors in cells A12-A14

I get a 1 in cell A2 and errors in all other cells

There is a download that works but the formula in the cell is surrounded by brackets { }

{=INDEX(SMALL(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, ROW($1:$10), ""), ROW(INDIRECT("1:"&SUM(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, 1, 0))))), ROUND(RAND()*SUM(IF(COUNTIF($A$1:A1, ROW($1:$10))=0, 1, 0)), 0), )}

The instructions mention a CRTL+SHIFT+ENTER but I do not know what this accomplishes nothing changes.

This is sooo close to what I need it hurts.

Help?

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[mintjulep]

The {} indicated an ARRAY FORMULA.

CRTL+SHIFT+ENTER is how to enter an array formula, but instructions are not quite right.

Copy only this:

=INDEX(SMALL(IF(COUNTIF($A$1:A1, ROW($1:$49))=0, ROW($1:$49), ""), ROW(INDIRECT("1:"&SUM(IF(COUNTIF($A$1:A1, ROW($1:$49))=0, 1, 0))))), ROUND(RAND()*SUM(IF(COUNTIF($A$1:A1, ROW($1:$49))=0, 1, 0)), 0), )

Select Cell A2.

Paste the above into the formula bar, then CRTL+SHIFT+ENTER

Copy down to A50

It seems to work, although I haven't tried to figure out how yet.

 

[Gavona]

Sounds like you are sorted. Just out of interest I got the approach I outlined to generate 100,000 results in another worksheet in 105 seconds. So a million in 17.5 minutes.

Sub Macro1()
'
' Macro1 Macro
Dim i As Integer
Dim j As Integer
Dim myRange As Range
Dim elapsedTime
Dim starttime

starttime = Time
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set myRange = Worksheets("Sheet1").Range("A5:B53")

For j = 1 To 28 Step 7
For i = 1 To 25000
    With myRange
        .Calculate
        .Sort Key1:=Range("A5"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End With
    Sheets("Sheet1").Range("A1:F1").Copy
    Sheets("Sheet2").Cells(i, j).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Next i
Next j
elapsedTime = (Time - starttime) * 24 * 60 * 60
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Elapsed time: " & elapsedTime & " sec."

End Sub

Gavin

 

[kwbMitel]

Too Cool!!! thanks so much

Allows me to show that all 49 are picked and I just choose the first 6. Awesome! Anyone can grasp that.

For what you've done, no question on the star.

For the extra mile award. How would I maniplulate it make the selections row by row. Transpose it so to speak.

I want multiple instances row by row (around 10,000)




*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[kwbMitel]

Gavona, my current solution generates 1 Billion an Hour and I consider that slow.

Yours would take 17,500 Minutes or roughly 5 hours

Not dis'n just observ'n

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[Gavona]

Impressive.

Gavin

 

[lionelhill]

Oh help. Have a look at this:

http://support.microsoft.com/kb/86523

If Excel can genuinely generate only about a million random numbers, that means that if you generate 1000 million series of numbers (no matter how complex the series) using Excel's generator, each sequence you generate will be repeated 1000 times.

It doesn't matter how many hours you spend repeating it. At some point, you're effectively just copying columns.

 

[xlhelp]

How many solutions do you need?

Here is one. Usage =PickNumbers(how many, start #, end #) eg PickNumbers(7,1,49)

Function PickNumbers(N As Long, Low As Long, High As Long) As Variant
'// By: Dana DeLouis
    Application.Volatile
    Dim grp As New Collection
    Dim v As Variant
    Dim Num As Long
    ReDim v(1 To N)
    On Error Resume Next


    With grp
        Do While .Count < N
            Num = Int(Rnd * (High - Low + 1)) + Low
            .Add 1, CStr(Num) ' 1 is a dummy
            If Err.Number = 0 Then v(.Count) = Num Else Err.Clear
        Loop
    End With 'grp


    With Application
        If TypeName(.Caller) = "Range" Then
            If .Caller.Rows.Count > 1 Then
                PickNumbers = .Transpose(v)
            Else
                PickNumbers = v
            End If
        End If
    End With 'Application
End Function

Second: Usage =QPick()

Function QPick()


'array to check uniqueness of selection against
Dim nums(1 To 49) As Boolean
'to be used to keep track of how many selections have been made
Dim i As Integer
'array to be used to store the six selections
Dim qp(7) As Integer
'string to display the six selections
Dim stNumbers As String


'set i to 0 because no selections of been made yet
i = 0


'repeat this loop until all six selections have been made.
Do While i < 7


    'initialize MSAccess random number generator
    Randomize
    'create variable to temporarily store random number
    Dim rec As Integer
    'create random number and assign it to rec variable
    rec = Int((49 * Rnd) + 1)
    'check uniqueness of random number
    If nums(rec) = False Then
        nums(rec) = True
        'if number is unique, assign it to qp array
        qp(i) = rec


        'append number to the stNumbers string
        If i = 0 Then
            stNumbers = qp(i)
        Else
            stNumbers = stNumbers & "-" & qp(i)
        End If


        'increment selection counter
        i = i + 1
    End If


Loop


qpick = stNumbers
'MsgBox (stNumbers)


End Function

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[dallen43]

The methods discussed above are interesting, but how does this in any way relate to lottery numbers?

The randum number generator in Excel may or may not be as randum as lottery balls.

Sorry, just a thought.

 

[strongm]

>They would understand my code much less

'They' would be fine. CajunCenturion (and myself) are both VB/VBA experts.

 

[kwbMitel]

Strongm - Still stand by my statement as I am not a VB/VBA expert so you would have much difficulty understanding my code from the point of view of an expert.

Why did he do that? That can't be right! What was that guy smokin' when he wrote this crap.

I can hear it now clear as day. ;-)

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[kwbMitel]

xlhelp: What you've provided is way out of my league. I can accept that it works but I would have trouble applying it to my application (I think)

Lionhill: I don't need a million random numbers and repitition is desired.

dallen43: I would expect a computer program to be more random than the actual lottery. This purpose is to achieve results predicted by formulae. To do so I must create millions (maybe billions) of tickets.

My tried and true method I understand, it works, but it's slow. Not to mention the fan on the processor gets a little workout for about an hour.

Thanks everyone. Especially mintjulep as I see future uses of that formula even if I couldn't figure out how to use it here.


*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.