Selecting Random Names With Excel

Random selections are necessary a surprising amount in day-to-day life. Most people opt for less than ideal solutions to the problem, drawing straws or names in a hat. This is ok when deciding who’s turn it is to buy the drinks but not so great when calling the non-randomness of your selection into question could land you in hot water.

Thankfully most of us have access to Microsoft Excel or an equivalent online service and spreadsheets have all you need to get the job done and shift the responsibility to an objective third party, even if it is one you program yourself.

I recently ran a prize draw where I needed to pull out a selection of names from a list of around 4,000. My original solution was a bit cumbersome, and I complained loud enough that readers came to my help.

With the assistance of Nicole Simon and Tony Rose I investigated better ways. Unfortunately their suggestion of using the RandBetween function threw errors on my Mac version of Office, so here is my new and tested approach.

Random SelectionExcel

The first column is an incremental number. You can easily add this in using the formula =ROW()-1 just type that in the first cell, copy, select how many cells you need, then paste. Excel will do the rest for you.

In the next column you add your names. Look over my selection and you will get an insight into my TV viewing habits ;)

Now comes the part we did all this for, the random selection formula:

=VLOOKUP(INT(RAND() * MAX(A:A))+1,A1:B$21, 2, FALSE)

Vlookup will find the value of a cell based on an index. In our case the index is the number in the first column. We don’t just want any number though, we want Excel to select it randomly, so we use the RAND() function multiplied by the max (in my example 20). We want it to be a whole number so we wrap that in INT(), and it needs to start at 1 so we add 1 to the result.

In the next part of the VLookup we tell Excel where to find all the data, and where to find the value we want to return. We enter false for the final attribute because we need an exact match.

There you have it, just paste that baby in or recalculate as many times as you need and you will get random selections returned.

Only minor irritation, it doesn’t stop it returning duplicates, but it’s not a big deal for my purposes.

Got a better solution? Share in the comments :)

Posted on August 7, 2008 by Chris Garrett 
Filed Under Excel Tips

Comments

9 Responses to “Selecting Random Names With Excel”

  1. John McGarvey (copywriter) on August 7th, 2008 3:18 pm

    The easiest method I’ve used also works in Excel:

    1. In column A, put your list of names
    2. On the first line of column B, type =rand(). This generates a random number.
    3. Drag the random number down the B column so that each name has a random numbe beside it
    4. Go to the data menu and sort by column B
    5. The top 5 names (or whatever) are your winners

    Sure, it’s quick and dirty … but it works!

    The immediate disadvantages I think of are it’s a bit inelegant, and there’s no way to audit the results or ‘prove’ that your selection was random.

    What do you think?

  2. Chris Garrett on August 7th, 2008 5:10 pm

    Seems like a nice solution, when I use a database I tend to ORDER BY a random number so it is pretty much the same :)

  3. AKA Riptide Furse » What I Learned about on August 7th on August 8th, 2008 3:31 am

    [...] Selecting Random Names With Excel : Codswallop – Random selections are necessary a surprising amount in day-to-day life. Most people opt for less than ideal solutions to the problem, drawing straws or names in a hat. This is ok when deciding who?s turn it is to buy the drinks but not so great when calling the non-randomness of your selection into question could land you in hot water. [...]

  4. Tony Rose on August 8th, 2008 4:03 pm

    Chris,

    And you thought my solution was a little complex… Vlookup, nested stmt, INT, etc.

    I’m not sure why this wouldn’t work on a MAC. For PC folks, simply use =RANDBETWEEN(1,4000)with Chris’ numbering method and that will give you the winner. Much easier than this formula.

    Always glad to help!
    Tony Rose

  5. Chris Garrett on August 12th, 2008 1:03 pm

    What can I say, I am a geek, I over-geek things :)

  6. Brad on August 12th, 2008 4:05 pm

    I also did the same.carry on guys…

  7. Jorge Camoes on August 22nd, 2008 10:05 pm

    I would prefer something like:

    =choose(x,table)

    where “table” is a named range with all the names (“B2:B21″) and x is a randomly generated number.

  8. Emily on October 7th, 2008 7:34 pm

    This is very helpful, but I have another question:

    How do I completely randomize an entire list of names with no duplicates?

  9. Eric Burk on February 11th, 2010 3:21 am

    Is there a way to add a weight to a name? If you were doing a raffle and someone bought 20 tickets, do you have to list them 20 times or can you take the number of tickets somehow into account?

Leave a Reply