Secret Santa

Hi All,

I'm trying create a secret santa excel spreadsheet for my extended family where people are randomly assigned to someone else. Easy, I hear you shout, you just need to order by random number.

Not so! I'd like to avoid those within a nuclear family being allocated to each other. I'd thought of using VLOOKUP to check the family of the person who's been assigned by a randomn number, and using IF to check if that person is in the same family. By summing the results of this IF, I thought I would have a single value which I wanted to be zero. I could then use solver to look for order solutions which would give me a zero value. But solver doesn't seem able to do this.

Does anyone have any ideas please? I have attached where I got to

Thank you in advance,

Tom

Best I can do w/out spending too much time is to set up an iteration. It does 4 passes, one for each family.

Note: your ranking method didn't work because randbetween can result in duplicates. I used one column for rand and one column for rank to reduce the chance of duplicates.

Keep pressing F9 to scramble the results. As long as you don't have an N/A in the "Test" column then you're good to go.

Unfortunately, it's hard coded, but you can see the methodology (if you have a different number of families or family members).

Thank you Masteff and shg, I will enjoy trying to understand what you did!

Tom

Hi folks ... I have the same requirement, but for a family group of 32. I've tried to insert rows/columns into the last referenced s/s, but the outcome is clearly awry !! I would really appreciate some help here, thanks in advance !!EF798223 - Secret Santa - 32 people in scope - v0.00.xlsx