Thread: Random Name Selection Model - "Pick a name out of a hat method!"

Hello -

I am creating a basic model for a manager in which I need to select, at random, winners for three different levels of prizes (Level 1 - 10 winners, Level 2 - 22 winners, and Level 3 - 44 winners).

On Sheet1, I have built the framework for the with three sections (one for each prize) including the spots where I would need the winners name to appear along with potential macro spot. On sheet2 I have the names of all eligible winners in A1:A76.

The goal is to create a function that will give the user the ability to press the "button" on sheet one which then pull, at random, from the list on sheet2 and pull the results into a desired cell on sheet1. Thereotetically, we would want to have the ability to press the button 10 times for the first prizes, 22 times for the second, and so on...

I would prefer to not assign the contestants random numbers using the rand or randbetween function. Rather, we are trying to create what, if any, suspense possible by having their names appear on the dashboard.

I really appreciate any possible guidance or thoughts to my process. Please don't hesitate to follow up with questions!

Thanks,

Scott

Mn...

1) If you change from automatic to manual recalculation, rather than a macro button, you can just press F9 once and excel chugs through one recalc. So, if you have some random number process, you can generate one set of numbers with one keystroke.

2) RAND and RANDBETWEEN are both... they're not super-great as far as pseudorandom number generators go, but on the other hand, they're not bad, either. I mean, I suppose you could import some other generator, but that sounds like a lot of work for a marginal gain in randomness.

3) Can people win twice? Can they win multiple levels? Can they win multiple times at each level? One of those is nearly certain, because...

4) 10+22+44 = 76 prizes... and you've listed 76 people. So do you want to assign each person to one category or what? That might be a better approach... have each person come up and get their prize level.

5) If you're looking for "suspense", rather than building what looks like a pretty complicated piece of code, printing out the list, chopping them up with scissors, and putting them in a (literal) big novelty hat would be better. I mean, it would be pretty cheesy, but that was already guaranteed.