Christmas is here again, and while we at SpreadsheetSolving appreciate the festive spirit, our practical side always wonders “does every single friend need a gift from each of their other friends?”. And… “why can’t we just give cash?”
The game Secret Santa somewhat lessens this gift giving burden among a large group of people. In this game, each person is secretly assigned one other person in the group to give a gift to. Typically, when the person opens their gift, they try to guess who gave it to them. Great fun is had by all (in theory).
We can create a simple spreadsheet to set up the random assignment of people to others in a group. First we enter in a hypothetical list of names:
Next we’ll create a list of random numbers with the Rand() function. Then we’ll rank the random numbers we generated from 1 to 17 using the Rank() function
Then we’ll use Vlookup() Function to lookup the name of the of the person whose number was randomly selected as a Giftee for each person.
The only thing is that there’s the chance someone gets themselves as their own Secret Santa. Let’s add a column that looks to see whether we assigned someone themselves, which would mean we would need to run the random number generator again (by hitting F9).
Once there is a good assignment, just copy the table and paste values to save it (or else the randomizer will re-run every time a key is pressed). One more thing, don’t forget to set a price limit!
Check out the spreadsheet: Secret Santa Spreadsheet