Create your own Lottery

Do you want to create and issue (distribute) randomly coded tickets to people - for any good reason you can think of? It could be e.g. some kind of a lottery or a game. Excel may be very helpful in achieving such a goal. 

Let's say you've decided to issue tickets marked with codes like this: A000 (one letter and three digits). This pattern creates 26,000 permutations, so you can issue 26,000 tickets with a different code each. Then, you build a list of only 5200 randomly selected codes in Excel worksheet (col. B in example shown below). You crate the list using this formula:

 =CHAR(INT(RANDBETWEEN(65,90)))&MID(RAND(),3,3)

and then copying all 5200 positions and paste them (in place) as values. You can remove from the list any recurring codes by selecting Data>Remove Duplicates (in col. B) in the ribbon.

This way you give the chance of selecting a winning ticket like 1:5 (on average).

When someone presents you one of the 26,000 issued tickets, you can easily check if the ticket code is present in the list of 5200 codes. If so, it means that it is a "winning" ticket. You do that by clicking on the control button "Check for Winners", as shown on the picture. The following Excel code (VBA macro) must be assigned to the button:

Sub CheckForWinners()
'Checks for winning lottery ticket codes
'and records the winning tickets in Col. A
    Dim cnt As Integer
    Dim rngA As Variant
    Dim rngB As Variant
    Dim cod1 As String
    Dim cod2 As String
    Set rngA = Range("A:A")
    Set rngB = Range("B:B")
    On Error Resume Next
repeat:
    cod1 = InputBox("Please enter your ticket code", "Check your ticket", "A000")
    If Len(cod1) <> 4 Then GoTo repeat
    cod2 = WorksheetFunction.VLookup(cod1, rngB, 1, False)
    If cod2 = cod1 Then
        MsgBox "Congrats! Your ticket is a WINNER.", , "Ticket " & cod1
        cnt = WorksheetFunction.CountIf(rngA, "*") + 1
        Range("A" & cnt).Value = cod1
    Else
        MsgBox "Sorry, your ticket is NOT a winner.", , "Ticket " & cod1
    End If
End Sub

Every winning ticket's code will be eventually recorded in col. A.

In this example, if you set the price of one ticket at $5, and the winner's prize at $20, you can earn in a long run $1 on each sold ticket... 😀