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!
