My sister called with this problem: Her son's grade school has 300 students and each are eligible to win prizes based on school and volunteer work. Students can earn 1 or more chances (much like a raffle ticket). She went on to say last year they wrote each student's name on a slip of paper and tossed them in a hat -- but with many students earning 15 or more chances, she said she'd never do that again.
Having raffle-like drawings can be easily automated in Excel with macros. If you have never written an Excel macro, this will be fun project and the instructions below should take less than an hour to write. When you are done, you will have a random name selector.
You can also download a completed version using this link.
Click on this Keyliner Public GDrive link:
PrizeOrama Excel Spreadsheet Download
Author's Note: Since this article was written, I developed a much more sophisticated prize selection / prize drawing program, which can be found at this link: Keyliner's PrizeSelect. This program is also free.
The completed data-entry sheet looks like this:
Winners are calculated in a result-sheet that can be displayed in a variety of different ways:
Constructing the Initial Sheets
1. In Excel, open a new workbook and name two of the sheets as "Names" and "NameResults". These names will be required by the macros. (Other-mouse-click the default sheet, "Sheet1" and choose Rename.)
2. As illustrated at the top of this article, type column headings on row 4:
A4 = "Name"
B4 = "Tickets"
Cell A5 (e.g. "Bobbie" contains the first test record)
Cell B5 contains the count of the number of tickets Bobby earned.
Continue to build a half-dozen test records.
(Optionally, if you are using numbered Raffle-tickets, type the lowest-numbered raffle-ticket in the first two cells (A5, and A6), then build a simple addition formula and fill-down to the largest-number; No need to put in a ticket-count, "1" is assumed.)
e.g.
65444 (in cell A5)
65445 (in cell A6 - to get past the Range-name in cell A5)
=A6+1 (in cell A7)
2a. Highlight cell A5 and "Range-name" the cell as "NameAnchor".
Select Formulas, Define Name
Name the cell "NameAnchor"
3. Expose Excel's Macro Development Tools:a. Click the Microsoft Office Orb (the old File Menu), choose the "Excel Options" button at the bottom of the screen. In the "Popular" section, check "[x] Show Developer tab in the Ribbon"
4. Build an initial Macro by following these steps:
a. From Excel's top menu "Developer," click "Record Macro"
b. For the Macro Name, type "SelectWinners" and "store macro in 'This Workbook'". No need for a shortcut key. Press Enter and the macro will begin recording, noting the small blue square in the lower-left of the screen.
c. Click the mouse in Cell A5 ("Bobbie") - just so the macro can record something.
d. On the toolbar, click "Stop Recording"
5. Open the Visual Basic editor (Macro Editor):
a. While still in the sheet, press Alt-F11 (Visual Basic VBA editor).
b. On the tree-side, tunnel to "Modules, Module 1". This is where the code that drives this program will live. You will see a Range-Select statement in the macro; this is what you recorded in the previous step.
At any time you can close the macro-editor (clicking the "X"); changes are automatically saved and you are returned to the sheet. Press Alt-F11 to return to the editor.
c. Above the "Sub SelectWinners()" line, insert a few blank lines and type these three statements:
Option Explicit
Public iRecordCount as Integer
Public iEntryCount as Integer
It will look like this:
For technical reasons, all macros should have an "option explicit" line -- even though Microsoft does not default it. The other two statements declare variables.
6. Write the main "SelectWinners" Routine:
This routine is the longest routine in the program and it will take a few minutes to type. As you type, indent with spaces and make the code look pretty. Comments (green) begin with tic-marks and long-lines can have line-continuation characters:
(click image for larger view; click right-x to return)Still within the same sub-routine, complete the code with these remaining statements. Both the code above and this code lives within the Sub/End Sub statements:
7. Write the Private Sub A100_ProcessRecords() routine:
a. Below the "SelectWinners" 'End Sub' line, press Enter a few times to insert blank lines, then literally type these commands in order to build a new routine:
The details within the "Do Until / Loop" will be filled in a moment. For now, just stub-in the routine.
b. Now fill in the Loop details for the A100 routine (this code lives within the Until/loop statements):
(Click for larger image; click "right-x" to return)8. Write the A200_SortNameResults routine:
Continue with the next routine, which is typed below A100's "End Sub". You are almost done:
9. Write the ClearPreviousResults routine:
Building a "Select Winners" Macro Button
As illustrated at the top of this article, it is handy to have a button that kicks off the macro. Build a button and attach it to the "SelectWinners" macro with these steps:
Close the macro editor and return to the original spreadsheet.
Click on the "Names" tab and follow these last steps:
1. Using the top-menu, "Developer"
a. Click the "Insert" tool and then the "button" Forms Control Tool. Be sure you do not select the ActiveX version.
b. Once the button is clicked, move the mouse into the sheet and click-and-drag an outline of a button somewhere on the form where you want a "Select Winner" push-button. Let go once the outline is in place.
c. When prompted for a macro name, choose "SelectWinners", then "OK". Note: You can Control-Click the button to resize, move and change the button's properties.
Alternately, you can run the macro manually by clicking the top "View" menu, Macros, Run Macro. Choose "SelectWinner" from the list.
Testing
To test the macros, confirm you have test (student) names in cells A5 and down. For a few of the records, type a "Ticket" count of 5.
Click the "Select Winner" button.
Open the "NameResults" tab (bottom tab) to see the randomly-selected winners. Note that those people with more than one ticket are represented multiple times in the list. Each has a random number, but all numbers were sorted randomly. If you have three prizes to give away, choose the top-three names. Remember, the results list shows all eligible people in one list -- with every ticket represented.
Click "Select Winners" a second time to re-randomize the list.
Saving the Sheet
If you are using Excel 2007/2010, sheets with Macros must be saved in a special way. Select File (orb), Save-As. Change the filetype from "Excel Workbook (*.xlsx)" to "Excel Macro-enabled Workbook (*.xlsm)" - you must do this or the macros will be stripped out of the sheet.
When you next load the sheet, you will be prompted to "enable macros". See Microsoft Help for additional information on how to always allow macros on this sheet.
Description of Steps:
This article is not intended to teach macro programming, but here is a brief description of the program's logic.
The main module, "SelectWinners" calls A100_ProcessRecords.
A100_ProcessRecords starts in cell A5 (the first name) and moves one record at-a-time down the list. With each found record, it copies the (student's) name to the second "NameResult" sheet. If the student has more than one ticket, their name is copied multiple times.
As each name is copied, it is assigned a random number.
Once all the names are copied, the second sheet is sorted numerically by the random number. Note how the sort routine knows how many records were loaded and it uses this count to highlight the sort records.
Once sorted, the top name should be awarded prize #1, second name prize #2, etc. Usually, the grand-prize is awarded third. The downloaded version of this sheet (see below), contains other interesting features.
Disqualify a person by putting a negative number for the count (-e.g. -1). With this, their name stays in the list, but they do generate a random number and in effect become ineligible for the raffle.
If you need to have the person's name and phone number, type both in column A.
Downloadable Version:
This spreadsheet is available on Keyliner's Public GDrive site. You can download a complete sheet, with all macros, free of charge and you are welcome to use this routine as you please. If you use the sheet, drop me a note; I'd like to hear from you.
PrizeOrama Excel Spreadsheet download
The downloadable version contains several other interesting features, not documented here, along with full instructions on the features and use. My apologies for not having cut-and-paste code in this article; I've never figured out how to make those types of textboxes....
Other comments:
- Remember, only click the Select Winners button one time -- all winners are randomized and calculated at the same time.
- If a person has multiple tickets or the person is listed multiple times in the list, they have multiple chances to win and can win multiple prizes.
- At this time, the sheet does not allow a person to win "only once" - even if they have multiple chances; this is a future enhancement.
- The downloaded version has many more features than this article discusses.
Related Links:
PrizeOrama Excel Sheet Download
PrizeSelect - a more complete raffle ticket program
No comments:
Post a Comment
Comments are moderated and published upon review. (As an aside, not a single spam has been allowed through; why bother?)