Podcast: Play in new window | Download

Subscribe: Spotify | TuneIn | RSS

Let’s say you have a set list of names (in this case TV characters from popular 90s TV shows). You want Google Sheets/Excel to generate a random list of names from your list as if you were picking names out of a hat. How would you do this? It most likely would involve the RAND function, but let’s take it a step further and say you want to give the end user the ability to dictate the number of random names to return from your list (e.g. out of my list of 100, give me 5 random names). This is the Google Sheet with all the completed formulas. In addition to the audio format of this episode, I’m also going to start releasing the video tutorial:

Create your list in column B

Start with your list of names in column B. This can be any list you want to randomize. My list is just a bunch of TV characters from shows I watched when I was a kid.

In column A, you put the RAND function and copy it all the way down to the bottom of our list. You’ll get a decimal with random numbers. Doesn’t look that useful now, but this random number column will drive the rest of the tool to generate your list of random names:

Sort this random list of numbers

It sounds kind of weird, why would you sort a random list of numbers? What does that even mean? As you have probably seen, every time you refresh your Google Sheet or commit an Excel formula by hitting ENTER, all those random numbers in column A will change. This means if you sort this list of random numbers, the sorted list will change too. I put a space in column C so in cell D2, you enter this formula:

The SORT function takes in a range of cells as the first parameter, the sort index as the 2nd (which is just the number column we ant to sort on, column #1), and then true or false for sorting in ascending or descending order. You can also put 0 to indicate false which is what I did in this example to sort in descending order.

The nice thing about the SORT function is that it automatically fills the formula down to the bottom of your data set. This is a relatively new function in Excel since it kind of acts like dynamic array formulas or array-entered formulas. The formula kind of “spills” down for you as your list grows so you don’t have to worry about dragging the formula down until the last row in your data set.

A good ‘ol VLOOKUP

What does this column of sorted random numbers do for us? Well, we know that each random number in this sorted column corresponds to one of the numbers in column A where we generated the random number. So in column E, we just do a VLOOKUP using column D as our lookup value and columns A:B as our lookup table to get the name associated with the random number in column D:

This is not the usual way you might use VLOOKUP because you’re usually using VLOOKUP with some unique identifier as the lookup value. Column A isn’t really a unique “TV character ID” since that “ID” changes all the time with the RAND function. We don’t really care about that, because now when you refresh the Sheet, column E will always have a random list of names:

In the above gif I’m just pressing COMMAND + R a few times to refresh the Sheet so that the RAND function in column A constantly changes.

We could stop here since you now have a random list of names in column E. Let’s take this a step further and give the end user the ability to choose the number of random names from the list.

User input with OFFSET

We’re already doing some hacking with VLOOKUP and using it in a way that it probably wasn’t made to use, so let’s do something similar with the OFFSET function. Cell H5 is just my “user input” cell where I’m getting the number of results from the user. This is a hard-coded number the user has to input. Then in cell H2, I have this OFFSET formula:

Let’s break this down by each parameter:

• E2 – This is the “starting point” for my OFFSET function
• 0 – I don’t want to move any rows up/down
• 0 – I don’t want to move any columns up/down
• H1 – References my user input cell indicating how many rows of data I want to return from my OFFSET (e.g. “height” of the range)
• 1 – How many columns to return (e.g. “width” of the range)

Now as you put a number in cell H1, the list of random names will grow and shrink. If you put a number that is more than the list of names you have, then it will just return the max number of names from your list (in random order, of course):

Picking the right tool for the job

A caveat I point out at the end of this episode is that while you can do this random list of names generator in Excel or Google Sheets, a spreadsheet may not be the best tool for the job. There are hundreds of random list generator apps that may be built specifically for your industry be it education or hospitality. Sometime it’s just easier to do it in a spreadsheet because all our data is there, but constantly question if the tool you are using is the right one for the job.

There’s a similar template in the Coda gallery which generates a random list of teams of players based on the number of teams and players you have. Just another nifty way at approaching the same problem in a different tool. Disclosure: I work at Coda.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• Google Cloud Platform Podcast #226: Documentation in Developer Practices with Riona Macnamara