Forum Discussion

Maree29's avatar
Maree29
Copper Contributor
Oct 05, 2020
Solved

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 se...
excel
  • NikolinoDE's avatar
    NikolinoDE
    Oct 06, 2020

    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)

     
JMB17's avatar
JMB17
Bronze Contributor
Oct 18, 2020

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. 

Maree29's avatar
Maree29
Copper Contributor
Oct 18, 2020

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.

Resources