Do you want to organize a Secret Santa swap with your friends but you don't know how to get everyone a partner quickly? Well this article is for you!
In case you are unfamiliar with some of the concepts we discuss in this article, there are links to each concept you will need to understand if you don't already.
Step #1: Establishing the Names
You will need two columns, one for the names and one for the assigned partners. This is as simple as running a simple copy and paste
Step #2: Randomize Range
To randomize the range, simply select "Data" and then click "Randomize Range".
That will rearrange and "shuffle" the cells so now each name should have a unique assignee. But the question remains, what if you are assigned to get a gift for yourself?
Step #3: Highlight the Duplicates
Thanks to Conditional Formatting, you will be able to use custom formatting to highlight duplicates. Follow these quick and easy steps
- Access Conditional Formatting (Format -> Conditional Formatting)
- Apply the range of the second column
- Select "Custom Formula is"
- Enter Conditional equation (=$D:$D=$C:$C). Essentially this means that for any given row, if the cell in column D equals the Cell in Column C, the formatting will take place.
- Select a color; this color will appear if the custom formula is activated.
- Select "Done"
From there, you will be able to see if your shuffle left behind any duplicates. If that is the case, simply rearrange the cells until there are no highlighted cells.
Step #4: Map Out the Automated Messages
Here is where we get a little deeper into it. Let's say you have a super long list and you don't want to worry about sending a bunch of people essentially the same message. That is where the CONCATENATE function comes in.
Bonus Step: Broadcast the Messages via SMS
If you want to, you can use a sheets add-on to send SMS messages depending on cell values. I use TextgBlaster, but I don't really have a favorite, as I don't use a lot of sheets extensions. To find those, simply Click Extensions->Add-ons->Get add-ons.
And there you have it, that is how you set up an automated secret santa drawing in Google Sheets! Merry Christmas and Happy New Year from us at 8020 Sheets!
