Random formula
- Thread starter Ufoo
- Start date
saurabhjaina211
New Member
Hi Ufoo,
The requirement is not clear. Please share more details.
Thanks,
Saurabh
The requirement is not clear. Please share more details.
Thanks,
Saurabh
Hello,
Imagine this. John, Doreen, Brian and Donald are siblings and have their own separate families, with different number of family members (i.e. John has wife and 2 kids, Brian has wife and 4 kids. Donald has wife and 1 kid, Doreen is still single but she is an adult). I want to design a random gift giving formula such that:
1. All adults will be assigned people to give gifts to. So, for example John may be assigned 2, or 3 random names
2. No repetition of gift giver or provider
3. Gift provider and recipient should not come from the same family. This means, John must not be assigned his wife or his child
4. Gift giver and recipient must not be the same person
5. All kids are below 18. So they will not be assigned name to give gifts to
6. No one in the whole group should miss a gift provider
I hope this is clear now. Please help. Thanks
eibi
Active Member
Ufoo...
I've got a solution to offer, provided that you can be a little flexible in your requirements:
First, I've dumped your data into a single master list, with a Family ID indicator and a binary Adult Indicator.
Next, I've used RAND() to sort the master list into a randomized list of gift recipients.
Finally, I've used a rotating pattern (in an array formula, executed with Ctrl + Shift + Enter) to assign a giver to each recipient, using the criteria you established (must be an adult of a different family).
Note -- like sorting dominoes, there is a cross-over effect in the middle family -- where one adult gives 3 gifts, and the spouse gives 1. I understand that this stretches one of your requirements (at least 2 recipients per giver)...I don't know any way to avoid this, but I think it will generally be limited to 1 bust per cycle; should be easy to catch.
See attached file.
I've got a solution to offer, provided that you can be a little flexible in your requirements:
First, I've dumped your data into a single master list, with a Family ID indicator and a binary Adult Indicator.
Next, I've used RAND() to sort the master list into a randomized list of gift recipients.
Finally, I've used a rotating pattern (in an array formula, executed with Ctrl + Shift + Enter) to assign a giver to each recipient, using the criteria you established (must be an adult of a different family).
Note -- like sorting dominoes, there is a cross-over effect in the middle family -- where one adult gives 3 gifts, and the spouse gives 1. I understand that this stretches one of your requirements (at least 2 recipients per giver)...I don't know any way to avoid this, but I think it will generally be limited to 1 bust per cycle; should be easy to catch.
See attached file.
Thank you so much for this solution. Very helpful. However, there is a minor problem. Sometimes a provider is not assigned a recipient at all, or 1 provider has say 3 recipients while another provider has just 1 recipient. Is there a way we can tweak the formula such that recipients are distributed to providers as equally as possible please? like say, 3 recipients for 2 provides and 2 recipients for the remaining 4 providers. again thanks a lot
Thanks a lot guru. The formula is working perfectly
Hello @eibi, can we tweak the formula to do a little bit more please?
1. Distribute adult and young gift receivers among gift providers so that no gift provider will get adults only or under 18 only.
2. When I press F9 the number of gift recipients per providors change. Sometimes some providers get no recipient. So I press until I get an equal number of recipients for all providers. Can't we get the formula to have a constant number of recipients per provider? In that case, when I press F9 what will change is recipients names and not the number. Just wondering aloud. Thanks again