Forum Discussion
Random selection for raffle winners
- With your permission, if I can recommend you, add a file (without sensitive data) to your project.
Explain your plans in relation to this file.
So you can get a solution that is tailored to your needs much faster.
At the same time, it is much easier for someone who wants to help to understand the subject.
A win-win situation for everyone.
Here is a VBA suggestion.
The list of names in column A can be of any length, but must begin in A1.
The second macro builds in the button to trigger the first macro.
Code:
Sub raffle ()
Dim rngBer As Range
Dim lngZ As Long
Randomize
lngZ = Cells (Rows.Count, 1) .End (xlUp) .Row
Set rngBer = Range (Cells (1, 1), Cells (lngZ, 1))
lngZ = CLng (Int (lngZ * Rnd + 1))
Cells (lngZ, 1) .Offset (0, 1) .Value = Cells (lngZ, 1) .Offset (0, 1) .Value + 1
End Sub
'************************************************* *
Sub button ()
With ActiveSheet.Buttons.Add (243, 24, 104.25, 45)
.OnAction = "Raffle"
.Characters.Text = "Start"
End With
End Sub
If you click the button with the right mouse button, you can move it to any position.I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
Sorry it took me a little while to get back to you.
I modified the workbook to use structured tables, which will make the lists easier to resize (Table Tools tab, Resize) and add more entries.
Also, I changed the formulas to use structured table references, so you should not have to change the formulas after resizing and changing your customer/horse lists.
I added a workbook open macro to turn on iterative calculation to save you a step (just don't save the workbook as an xlsx file as that will strip out the macros).
JMB17 Hi again. I got it to work well when the number of buyers was equally divisable by 24. But when it was an odd number such as below I had issues.
I resized the table to 207 sweeps buyers and followed your instructions but for some reason there are some gaps from Sweeps buyers 193 - 207. These final rows remained as false.
Perhaps to make this work, I will need to sell the number of sweeps tickets that will make it divisable by 24 exactly since I am trying to replicate what happens in the real world of 'sweeps'.
You have been a terrific help to me in this so thanks. When we get closer to the date I will come back to you with how I am going with the number of sales. I have put a deadline of 12 noon on Mon 2nd Nov for me to finalise allocations then I am going to send 24 emails to the buyers advising them of their horse. The race itself takes place the following day on Tues 3rd Nov. Fun hey?
- I'm glad you have it sorted out and good luck with your event!
JMB17 Thankyou for being so quick to respond.
Each ticket is unique and a person can buy multiple tickets.
Therefore 1 set of 5 numbers is the way to go.
JMB you have been wonderful. Not sure if against the rules but can you tell me your first name. If so I can publicly recognise you for your input. I don't even know if you are in Australia, but if you are, you will know about the Melbourne Cup.
Regards
Maree
- I think the question is if one person can win more than once with the *same* ticket versus winning more than once on a different ticket.
If you were drawing numbers out of a hat, are you putting the number that was drawn back in the hat?
If they can win more than once on the same ticket, then I think 5 sets of 1 number would be correct. If they cannot win more than once on the same ticket, then I think 1 set of 5 unique numbers would be correct. JMB17 Hi again. Using Random.org was a good idea. Since I am trying to replicate the drawing of winners for 5 prizes, I used the Integer generator using 5 sets (5 draws) with one unique integer. I think this replicates how a raffle draw would work the best. Probably even better because often when you do raffle draws a person can win more than once. Could you confirm my thinking is correct. Very useful having someone to bounce things off.
And with the horse program, I am doing 4 sets of 24 horses (96 tickets) as this is how a sweep would work in the physical world.
Once again, many many thanks for your help. I have learnt so much...Maree
- Sorry for the late reply, I've not been on here this week. For a raffle drawing, I would suggest using random.org. They have a random number generator on their home page that is free - number your list 1 to 62 and use that for the min/max for the random number generator.
If the same number cannot win more than once, then scroll down to the "Numbers" section of the home page for the integer set generator, which you can use to generate a set of 5 unique numbers between 1 and 62. Guess what I am back again. I have the sweep worked out but now I need a random generator for the raffle. I thought we would have been able to do this raffle draw 'in person' but it wasn't to be so I will be doing the draw in Excel. I didn't want to play around too much with your Sweep code for fear of messing it up. But could you show me how to do a simple random selection on ticket numbers. In my sample attached I have 62 people who have purchased a raffle ticket. I exported the unique ticket numbers from excel. So I need to randomly draw out 5 names - 5th Prize, 4th Prize, 3rd Prize, 2nd Prize and then 1st Prize. Some people have purchased multiple raffle tickets. I am guessing that part of what you have already provided me will work.
I promise this is the last time I will bother you. Thankyou again ! Maree
JMB17 Many thanks for explanation. I have learnt alot during this but I shall leave such work to experts like you. Great job and thanks for being so responsive. I will give you an update when my event is imminent. You may even want to buy a sweeps ticket here https://www.trybooking.com/events/landing?eid=667369&
Cheers !
Maree
The file contains circular references by design (otherwise, the random function would never "lock" and just keep recalculating). In order for the formulas to calculate, formula iteration must be enabled (File/Options/Formulas - Enable Iterative Calculation.
When the option is not enabled, you will get the circular reference error. The workbook contains an auto open macro to enable iterative calculation, but it doesn't run before excel attempts to calculate the formulas - so you may get the circular reference error before the auto open macro enables iterative calculation (but should be able to just click through it).
If it doesn't appear to be calculating, double check that option is enabled.Hi again, I am putting this spreadsheet through the hoops but I think it is great.
I extended the number of buyers to 212 sweeps buyers by dragging the last few rows down but when I hold the F9 key it came up with an error to do with a circular reference. I still think the result was correct though. It might have something to do with how I am copying the rows down. I have attached the error and the spreadsheet again.
So to make it completely fair to buyers and to be truly random so it replicates drawing a horse 'out of the hat', I think the number of buyers needs to be divisable by 24, that way each horse is allocated the same number of times. If you could fix the error message I think that will fine, I have already taken up alot of your time.
Thanks again for your help, much appreciated.