The requirement is not clear. Please share more details.
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 lotUfoo...
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.
Hello @eibi, can we tweak the formula to do a little bit more please?Ufoo,
After too long to wait...see attached.