EXCEL 365 Random Number Table with no repeats and in Random order

R

robreich

New Member
Joined
Oct 27, 2017
Messages
31
I have watched the video on how to generate a table of random numbers with no repeats with the each set of 15 numbers in order. I would like to be able to generate a table with totally random numbers with no repeats and in random order. I am trying to reduce the time that it takes for a Charity Function to run a reverse raffle with 500 - 600 numbers. Right now, we do it with a rotating drum and pull out 10 numbers at a time. This takes a long time and I thought maybe I could create a table to mimic the act of pulling the numbers. However, they would have to be completely random in order. We pull 10 at a time and every tenth number receives a gift card. The last four numbers drawn receive cash prizes totaling $1,000 dollars. My Table would be 10 columns wide and as many number of rows needed to accommodate the total number of tickets sold.

Any help with this would be greatly appreciated.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Welcome to the forum.

The easiest way to do this is to put a list of your ticket numbers in column A, then put =RAND() in each row in column B, then sort by column B, then just take every 10th number from the list.

If you want something more like what you described:

ABCDEFGHIJK
Number of tickets
start #
end #

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]198[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]195[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]182[/TD]
[TD="align: right"]116[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]178[/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]197[/TD]
[TD="align: right"]134[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]184[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]179[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]177[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]194[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]189[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]161[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]119[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]149[/TD]
[TD="align: right"]188[/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]112[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]156[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]186[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]171[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]183[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]176[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]193[/TD]
[TD="align: right"]173[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]185[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]121[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]198[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]122[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=IF(C3-C2+1<>C1,"Numbers don't add up!","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]{=IF((ROWS($B$4:$B4)-1)*10+COLUMNS($A5:A5)>$C$1,"",SMALL(IF(COUNTIF($B$4:$K4,ROW(INDIRECT($C$2&":"&$C$3)))+COUNTIF($A5:A5,ROW(INDIRECT($C$2&":"&$C$3))),"",ROW(INDIRECT($C$2&":"&$C$3))),RANDBETWEEN(1,$C$1-(ROWS($B$4:$B4)-1)*10-COLUMNS($A5:A5)+1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]





Put the number of tickets sold in C1, the lower and upper ticket numbers in C2:C3. Then put in the B5 formula, and confirm with Control+Shift+Enter. Then copy the formula to C5:K5, then down as many rows as you need. Recalculate the draw by pressing F9.

Hope this helps.
 
Upvote 0
Eric,

I followed your instructions and was able to replicate the Table that you generated. At first, I was getting #NUM errors in a few cells, but then I copied your formula directly into my spreadsheet and it worked fine. I must have had something slightly wrong in how I had typed your formula in.

I will try to create a larger table like we would have. If I have any further issues or questions, I will post here. Thanks a lot for your help.

Bob



Eric W said:
Welcome to the forum.

The easiest way to do this is to put a list of your ticket numbers in column A, then put =RAND() in each row in column B, then sort by column B, then just take every 10th number from the list.

If you want something more like what you described:

ABCDEFGHIJK
Number of tickets
start #
end #

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]198[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]195[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]182[/TD]
[TD="align: right"]116[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]178[/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]197[/TD]
[TD="align: right"]134[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]184[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]179[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]177[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]194[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]189[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]161[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]119[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]149[/TD]
[TD="align: right"]188[/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]163[/TD]
[TD="align: right"]112[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]156[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]186[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]171[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]183[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]176[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]193[/TD]
[TD="align: right"]173[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]185[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]121[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]198[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]122[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]=IF(C3-C2+1<>C1,"Numbers don't add up!","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B5[/TH]
[TD="align: left"]{=IF((ROWS($B$4:$B4)-1)*10+COLUMNS($A5:A5)>$C$1,"",SMALL(IF(COUNTIF($B$4:$K4,ROW(INDIRECT($C$2&":"&$C$3)))+COUNTIF($A5:A5,ROW(INDIRECT($C$2&":"&$C$3))),"",ROW(INDIRECT($C$2&":"&$C$3))),RANDBETWEEN(1,$C$1-(ROWS($B$4:$B4)-1)*10-COLUMNS($A5:A5)+1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]





Put the number of tickets sold in C1, the lower and upper ticket numbers in C2:C3. Then put in the B5 formula, and confirm with Control+Shift+Enter. Then copy the formula to C5:K5, then down as many rows as you need. Recalculate the draw by pressing F9.

Hope this helps.
Click to expand...
 
Upvote 0
I played around with this table up to about 700 tickets. Once you get over about 300, it starts taking a while to calculate. It will depend a lot on what your PC is capable of. If it takes too long, try the sort method. You could even make it a bit simpler by putting =MOD(ROW(),10) in column C. Then you can filter column C and only show rows with a 0 in that column, which would be the list of winning tickets only. In any case, let us know how it goes.

Good luck!
 
Upvote 0
Eric W said:
I played around with this table up to about 700 tickets. Once you get over about 300, it starts taking a while to calculate. It will depend a lot on what your PC is capable of. If it takes too long, try the sort method. You could even make it a bit simpler by putting =MOD(ROW(),10) in column C. Then you can filter column C and only show rows with a 0 in that column, which would be the list of winning tickets only. In any case, let us know how it goes.

Good luck!
Click to expand...


Eric,
I found the same thing - I built the Table to address 450 tickets and it was slow. However, once it had cycled through all cells, then it ran faster if I re-calculated it. We can't only look at winning tickets, so your second suggestion can't be used. We will have a large board set up with all of the tickets on the board in numerical order. As ticket numbers are drawn, they are removed from the board. People at the Event always are looking to see if they are still in the running for the big money. Thanks for suggesting it.

Can you explain briefly to me what the function of each segment of the long formula is? I have worked with EXCEL since the original DOS version, but have not been too involved in some of the new functions. i like to be able to understand how the formula is working so that if I should have to make some changes down the road, I can do it. For instance, they could decide to change from 10 tickets drawn at a time to 12 tickets, etc. I was able to figure out that the first section of the formula appears to determine how many cells will have to be filled with a number to match the number of tickets in C1. However, after that, I was not able to determine what was being done, i.e. how the formula assures that there are no duplicates, etc. The man who is in charge of the Raffle is less computer literate than I and he has already stated that we have to show how we are avoiding duplicates, how we are making sure that every ticket number is in the Table, etc.

Any information would be greatly appreciated.

Bob
 
Upvote 0
The basic idea of the formula is pretty simple, even if the actual execution of it is tricky. Every time we pick a new number, we look at all the numbers previously picked, and exclude them. Then randomly pick a number from those remaining. So if we are choosing 1-10, and 2, 5, and 9 have already been picked, we create an internal array {1,"",3,4,"",6,7,8,"",10}. Then we randomly pick one of those numbers using SMALL (since SMALL ignores non-numeric values), and RANDBETWEEN(1,7). The 7 we get from 10 numbers to start with, minus 3 that we've already picked.

This construct:

ROW(INDIRECT($C$2&":"&$C$3))

is Excel's way to create an array. So if C2 is 3 and C3 is 7, it will return {3,4,5,6,7}. That's used 3 times in the formula. The first time is in the first COUNTIF. That one checks all the completed rows above the current cell to see if the number is found. The second COUNTIF checks to see if the the number is found in the same row before the current cell. If either of those is 1, then we put a "" in the output array, otherwise we put the actual number in the output array.

Then to find out how many numbers we've already found, we multiply the number of completed rows (ROWS($B$4:$B4)-1) times the number of tickets per row (10), and add the number of tickets prior to the current cell (COLUMNS($A5:A5)). That's used in the RANDBETWEEN as well as the initial IF to avoid all the calculations if they're not needed.

So that's the gist of the formula.


Another option would be to write a macro to perform your random draw. If you want to try that:

1) Open a new workbook
2) Press Alt-F11 to open the VBA editor
3) From the menu, select Insert > Module
4) Paste the following code into the window that opens:

Code: 
Sub RandomDraw()
Dim L As Long, H As Long, T As Long, r As Long

    L = InputBox("Enter the low ticket number:")
    H = InputBox("Enter the high ticket number:")
    T = InputBox("Enter the number of tickets per row:")
    
    Application.ScreenUpdating = False
    Cells.ClearContents
    Range("A1").Value = L
    Range("A2").Value = L + 1
    Range("A1:A2").AutoFill Destination:=Range("A1:A" & H - L + 1)
    Range("B1:B" & H - L + 1) = "=RAND()"
    
    With ActiveSheet.Sort
        .SetRange Range("A:B")
        .SortFields.Add Key:=Range("B1")
        .Apply
    End With
    
    Columns("B:B").ClearContents
    
    For r = 1 To Int((H - L) / T) + 1
        Range(Cells(r, 3), Cells(r, T + 2)).Value = WorksheetFunction.Transpose(Range(Cells((r - 1) * T + 1, "A"), Cells(r * T, "A")).Value)
    Next r
    Columns("A:B").Delete
    
    Application.ScreenUpdating = True
    
End Sub
5) Press Alt-Q to close the VBA editor
6) Back in Excel, press Alt-F8 to open the macro selector
7) Choose RandomDraw and click Run.

This will ask you for your low/high ticket numbers, and how many tickets per row. It has the advantage that it runs nearly instantaneously. It's just an automated version of the sort method I described earlier.

Hope this helps!
 
Upvote 0
Eric,

Thank you very much. I am definitely going to try writing the macro. This is what I used to do a lot of, but using older versions of EXCEL Language. I was Chief Estimator for a Construction Company and had a large EXCEL Spreadsheet that was completely macro driven. A lot of guys that worked for me told me I should sell the spreadsheet to one of the software companies that serve small construction companies, but I never got around to it. We ended up over the years with over 1,500 rows of Code as we made modifications and additions to the program.

Really appreciate your help. I will let you know how my attempt to rekindle the programming juices goes. I worked for quite a few years with Office 7 and just recently upgraded to 365, so I am re-learning a lot of stuff and enjoying playing with the systems.

Bob
 
Upvote 0
Here is another macro for you to consider. There is not really any noticeable time difference between my code and Eric's... the main difference is that I perform all of my calculations in memory and only visit the worksheet for the outputting of the grid whereas Eric does more manipulation of the worksheet itself and less in memory.
Code: 
[table="width: 500"]
[tr]
	[td]Sub RandomDraw()
  Dim L As Long, H As Long, T As Long, r As Long, Cnt As Long, OldUB As Long
  Dim RandomIndex As Long, Tmp As Long, Nums As Variant
  
  ' Make sure the number will always be random each time macro is run
  Randomize
  
  ' Get information to build grid
  On Error GoTo NoNumberEntered
  L = InputBox("Enter the low ticket number:")
  H = InputBox("Enter the high ticket number:")
  T = InputBox("Enter the number of tickets per row:")
  On Error GoTo 0
  
  ' Get an ordered one-dimensional array of all the numbers
  Nums = Evaluate("TRANSPOSE(ROW(" & L & ":" & H & "))")
  
  ' Randomize the ordered one-dimensional array of all the numbers
  For Cnt = UBound(Nums) To 1 Step -1
    RandomIndex = Int(Cnt * Rnd + 1)
    Tmp = Nums(RandomIndex)
    Nums(RandomIndex) = Nums(Cnt)
    Nums(Cnt) = Tmp
  Next
  
  ' Distribute the random array of numbers into the grid
  Application.ScreenUpdating = False
  Cells.ClearContents
  OldUB = UBound(Nums)
  ReDim Preserve Nums(1 To T + UBound(Nums))
  For r = 1 To OldUB Step T
    Cells((T + r - 1) / T, "A").Resize(, T) = Application.Index(Nums, 1, Evaluate("TRANSPOSE(ROW(" & r & ":" & r + 20 & "))"))
  Next
  Application.ScreenUpdating = True
  
NoNumberEntered:
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Rick,

Thanks. I will try this one as well. I have run his several times with up to 500 tickets and it is almost instantaneous even at that number. Now I need to figure out an easy way to get the numbers (10 at a time) into a PowerPoint presentation so we can simulate the drawing of 10 tickets at a time!! A lot of copy and paste, I think.

Boy, you guys are light years ahead of me in EXCEL, but I have been retired for 6 years now. I used to think I was pretty good with it and being able to make it do a lot of stuff!! But the software has been improved immensely in the last several versions too. My new challenge is to figure out what they replaced the old GOTO statement with!!

Bob
 
Upvote 0
robreich said:
Now I need to figure out an easy way to get the numbers (10 at a time) into a PowerPoint presentation so we can simulate the drawing of 10 tickets at a time!! A lot of copy and paste, I think.
Click to expand...
I don't us PowerPoint, so I am not sure what you will need there, but I will point out that once the grid has been created, you can execute this in the VB editor's Immediate Window...

Intersect(Columns("J"), ActiveSheet.UsedRange).Copy

and then, for example purposes, open NotePad and press CTRL-V to paste all the numbers from Column J into it. Perhaps you can make use of that procedure in PowerPoint to cut down on your work. I would also think it possible to open PowerPoint from within the macro and run a loop to output the values from part of the grid to whatever objects are available within PowerPoint and, perhaps, eliminate any copy/pasting. I am sure at least one of the volunteers here must be familiar with how to do this. If you get no takers here in this thread, you might want to create a new thread mentioning that have a grid of numbers and want to get a column of them into PowerPoint and indicate where in PowerPoint you want to place them. Normally, I would not recommend starting a new thread, but moving data from Excel to PowerPoint is different enough, I would think, to make it worth a new thread where volunteers familiar with PowerPoint could see it (they more than likely are not following this thread, so they would not see your new PowerPoint needs).
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
[Excel 365] Maximizing unique combinations of words - No repeats between any two words
Replies
4
Views
349
waffle
W
C
  • Question Question
Repetitive ”randomly” assigning/selecting team members for a task based on previous number of selections/assignments.
Replies
0
Views
533
ciprinitu
C
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
442
Anthony47
A
Rikki Tikki Tavi
  • Solved
Is this possible?
2 3
Replies
29
Views
2K
Peter_SSs
Peter_SSs
T
  • Solved
Weighted lottery help?
2
Replies
14
Views
4K
bebo021999
B

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

Share this page

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

Which adblocker are you using?

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
Back
Top