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)
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.
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.