Random winner list with no duplicates

Hello, guys,

I've been trying to put together a small tool that helps me select a list of winners from a total pool of participants, without having duplicates in the list. Based on inputs from fellow ExcelForum members I've got something going but it still has some kinks, in essence it selects random winners, puts them to where I need them do be in the output but there are still duplicates.

As a summary, I put winner data on sheets 2 & 3 and use the top 2 buttons from sheet 1 to populate the list starting from Sheet1:A1 -> F(as many entries as there are from the rest of the sheets). For this I pull in first name, last name, phone number, a receipt number (a purchase they made) plus an identifier I put in to be able to quickly find that particular entry on the 2nd and 3rd sheets.

The large button then starts the PickNamesAtRandom() function which I had help with from a fellow user, it has a statement for duplicates but it doesnt seem to do the trick.
In the O9 cell i put in how many winners I want it to pick and after calling the function it starts populating results in the N12 - R(how many entries I need) table.

I've set up conditional formating to check for duplicates (it should do this based on the phone numbers, judging that I have different rows of information I thought it to be the best identifier of a valid entry) and even if I ask it to provide 10 winners from 100 total it still has instances where it gives duplicates.

Any help would be much appreciated. Thank you,

RANDOM WINNER PICKER.xlsm

Hello, thank you, is there any way to have this as a static result and not have it recalculate every time I make a change to the sheet? What would be awesome is to have it integrated in the scrip so that it triggers only on pressing the button and have the results unchanged even if I modify something else on the sheet. Thank you

Thank you, I'll wait for a response from a VBA expert, hope someone can help.

Guess there are no VBA experts around?

Aleeko, I used the formula provided by AliGW and turned off the auto refresh. To do this you click on Formulas\Calculate Options\ and toggle to Manual. You can click F9 to refresh or click the button you created. Note that any formula on the sheet will not update until clicking F9.