Final Friday Fix: December 2022 Challenge

Merry Christmas and Happy New Year!  On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

Merry Christmas and Happy New Year, everyone!  It is the time of the year again for many to get together and celebrate the holiday season.  The gift-exchanging ritual may be an indispensable part for some.  So how do we usually exchange a gift?  Well, some might say Secret Santa is the way to go.  Hence, we will take this opportunity to issue a challenge for you to build a Secret Santa game.

For those who are not familiar with the term “Secret Santa”, this game essentially is a game where everyone writes down their name on a piece of paper and each player draws a random name out of a hat and you will be the Secret Santa for that person on the piece of paper.

We challenge you to rebuild the Secret Santa game using only the Excel formula.  You can download the question data here.

This December’s challenge is to write some formulae to split a list of players into pairs of senders (Secret Santa) and recipients of gifts.  The Players table is as follows:

As always, there are some requirements:

• this is a formula challenge; no Power Query / Get & Transform or VBA
• a maximum of one [1] helper column is allowed
• the formula(s) should be dynamic enough to update when a new name is added
• a gift’s sender cannot be the same as its receiver
• each person should receive one and only one gift.

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!