## 26 November, 2022

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:
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... ðŸ˜€