Random name picker

[Jak68]

Hello

I hope someone can help me quickly

I am looking to create a spreadsheet to be used once a month for a prize draw we do for 3 random names.

I get sent a list of around 100 people and each month I need to randomly pick 3 names from that list (1st prize, 2nd prize and 3rd prize).

Is anyone able to help me

Thanks

 

[Tim_Excel_]

I made this code for a nation wide lottery a few months back. I think this can help you

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Winners = 3
PopulationNumber = 100
file = 'workbook name
sht = 'sheet name


For i = 1 To Winners

opn:
    Randsample = Int((PopulationNumber- 2) * Rnd + 2)
    If dict.Exists(Randsample) Then GoTo opn
    dict.Add Randsample, i


    Workbooks(file).Worksheets(sht).Rows(Randsample).EntireRow.Copy Destination:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Range("A" & i)


Next i


End Sub

Please note that some of these variables need to change depending on your file. I had a userform attached to my workbook which required the user to fill in how many nominees and winners there would be. The code also imports information from the workbook containing the nominees to the workbook that contains the code, to prevent data from being overwritten accidentally.

 

[Jak68]

Thanks for the reply

Now i just need to figure out to set it up correctly as not very savvy regarding macros in Excel...

 

[Tim_Excel_]

I'd recommend you making a UserForm with a ComboBox for the workbook where your data is in (and if necessary another for the worksheet), and a ComboBox for the number of winners. If the latter is consistent, you can just state the number in the code like I did in the code above. I would determine the number of "players" by checking the last row in the WS. Good luck.