Secret Santa

[tpw101]

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