Forum Discussion
Random selection for raffle winners
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?
- 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)
22 Replies
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)
- You are welcome
I was pleased to be able to help you.
Nikolino
I know that I know nothing (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)
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.
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.
- 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)
