WeThotUWasAToad
asked on
Excel formula(s) to randomly assign sibling names for holiday gift exchange
How would you construct Excel formulas to accomplish the following?
I received a call from my son yesterday who informed me that he and his siblings (including spouses) would like to do a holiday gift exchange and asked if I would randomly come up with a name for each person using the following rules:
• each person is to give one and only one gift
• each person must receive one and only one gift
• no one can be assigned their own name
• no one can be assigned the name of their spouse (if applicable)
I have four children and all but one are married. So if each person is represented by a letter, it would appear as follows (with my biological children in the left column and their spouses on the right):
A — B
C — D
E
G — H
Thanks
I received a call from my son yesterday who informed me that he and his siblings (including spouses) would like to do a holiday gift exchange and asked if I would randomly come up with a name for each person using the following rules:
• each person is to give one and only one gift
• each person must receive one and only one gift
• no one can be assigned their own name
• no one can be assigned the name of their spouse (if applicable)
I have four children and all but one are married. So if each person is represented by a letter, it would appear as follows (with my biological children in the left column and their spouses on the right):
A — B
C — D
E
G — H
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't think there will be an efficient solution in Excel.
There are 7! = 5040 permutations of people involved, but very few (probably on the order of 5! =120) that will yield valid gift exchange lists.
You can do an exhaustive search with Python since it has a permutation generator.
I will give this a try, but probably not until the weekend.
It is an interesting problem.
There are 7! = 5040 permutations of people involved, but very few (probably on the order of 5! =120) that will yield valid gift exchange lists.
You can do an exhaustive search with Python since it has a permutation generator.
I will give this a try, but probably not until the weekend.
It is an interesting problem.
Write the names of the seven givers on a big piece of paper.
Write the names of the seven recipients on index cards.
Shuffle the cards, deal them out, and check for conflicts.
I expect you will need at least 25 and maybe as many as 100 to find a solution.
There will be three classes of solution:
7 person circle
5 person circle and one pair
3 person circle and two pairs
Write the names of the seven recipients on index cards.
Shuffle the cards, deal them out, and check for conflicts.
I expect you will need at least 25 and maybe as many as 100 to find a solution.
There will be three classes of solution:
7 person circle
5 person circle and one pair
3 person circle and two pairs
ASKER
There are 7! = 5040 permutations of people involved, but very few (probably on the order of 5! =120) that will yield valid gift exchange lists.
I don't need to know all possible solutions if I understand what you're saying. On the other hand, if even getting single solutions (using Excel) requires "weekend" time, then feel free to disregard. In other words, the question as I asked it in the OP is not something I need but rather something I'm curious about.
Thanks
For the cards you could also use four kings and three queens (other vice versa)..
ASKER
Write the names of the seven…
As mentioned earlier, the purpose — or even really a purpose — of this thread is not to know how to come up with name assignments that meet the criteria, but specifically to discover how it could/would be set up to be solved using Excel.
I expect you can find a few solutions by hand in 5 to 10 minutes. That would hold you for a couple of years.
I think I can find all the solutions in an hour with Python.
I don't know how to even approach the problem with Excel. It's just a bad match.
I think I can find all the solutions in an hour with Python.
I don't know how to even approach the problem with Excel. It's just a bad match.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As a quick follow up, you can easily do a table on the numbers of 10,15,20,25,30,40,45 with the names of your children and spouses and then do a vlookup to quickly see who is matched with who. That way you would not have to "manually" figure that portion out. I did not include in my original workbook, but very easy to do.
psteff for a user friendly solution in Excel when certain people thought it was impossible
d-glitch for the complete solution in Python
phoffric for the first solution
d-glitch for the complete solution in Python
phoffric for the first solution
ASKER