SOLVED

Random selection for raffle winners

Copper Contributor

I am running an online event where I will have a list of raffle purchasers. I have seen in previous settings where excel has the functionality to run a program to select a winner randomly. One can see the program scrolling through all the names and then eventually landing on a winner. 

 

Can someone tell me how to run such a program?

22 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Maree29 

 

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)

 
Select random generator from different names Additional information with formula: On this page you will find information on how you can do this with formulas: https://www.extendoffice.com/documents/excel/2768-excel-select-random-names.html I would be happy to know if I could help. Nikolino I know I don't know anything (Socrates)
You are welcome
I was pleased to be able to help you.

Nikolino
I know that I know nothing (Socrates)

@NikolinoDE 

 

Dear Nikolino, I am afraid the random function is not appropriate. I am not a 'coder' as such I need a formula that will provide the following:

 

If I have 120 Melb Cup Sweep purchasers and I have 24 horses in the race, If all these 24 horses names were put into a hat and each purchasers pulled out a name, I need for the 24 horse names to be randomly allocated to the 120 purchasers. There are 3 placings - 1st, 2nd & 3rd. When the race is run, there should be 5 people with the winning horse, 5 people who get second and 5 people who get third. 

 

So I need a formula that firstly allocates the 24 horses to the purchasers but I need this to replicate as if they were being picked out of hat. So the Random function may not work as it doesn't do this. Plus it keeps changing the random number allocated which is not what I want. 

I thought I might just allocate the horses to the purchasers based on date of purchase starting at Horse 1

See my file attached.

@Maree29 

 

I think you can do that with a circular reference and with iterative calculation turned on (File/Options/Formulas - Enable iterative calculation - I set mine for 200).

 

To generate a new list, you can change the "Reset" drop down to TRUE, then hold down the F9 (calculate) key for a few seconds to randomize the list. Then, change the "Reset" to FALSE and hit/hold the F9 key until each horse appears 5 times (the "Condition Met" cell should change to TRUE).

 

If it were me, once the list is generated, I would copy/paste special value the list to another worksheet just to ensure I didn't accidentally trigger the formulas to recalculate later and cause the list to be re-shuffled.

@JMB17 

I am sorry to bother you again but can you tell me how to do the second part

ie. Reset dropdown. I can't locate it.

 

It might be easier if you could make the change in the file that I sent. And I agree moving the final horse list to a new worksheet is what I would do also.

Regards

If you click on cell B1, you should see a drop down button with True/False options.

@JMB17 Hi Again, you are so clever. I would never have been able to work that out. I got the spreadsheet to output 5 horses for the selected buyers. So if I end up with say 300 buyers do I have to change the settings in formulas. You had put a setting at 200. Will I need to change that depending on the number of buyers I end up with ?

Is there anything else I should know. To ensure I don't mess up your formulas, I will just amend your spreadsheet and copy the buyer names to column A. If then I just update the Horse List values with the actual horse name, once I know it, will that impact the formulas. I am hoping not since it is referencing a cell.

It seems so much easier to cut up the names and put them in a hat. We would be doing that if we weren't living with a pandemic !! Thankyou so much for your help. You are amazing.

@Maree29 

 

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?

 

@Maree29 

 

Sorry, I assumed all horses would be equally allocated. With 207 sweeps buyers and 24 horses, each horse should be allocated 8 times with 15 horses allocated 9 times?

 

I reworked it to accommodate this remainder. 

@JMB17 

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.

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.

@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

@Maree29 

With the permission of everyone involved, here is an additional variant that had to be adapted as required.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

@Maree29  Help if I added the file....Maree

@JMB17 

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

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.

@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

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Maree29 

 

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)

 

View solution in original post