# Random formula

#### Ufoo

##### Member
Hello excel gurus, please see the attached sheet and help. Thanks

#### Attachments

• 11.3 KB Views: 19

#### saurabhjaina211

##### New Member
Hi Ufoo,

The requirement is not clear. Please share more details.

Thanks,
Saurabh

#### Ufoo

##### Member
Hi Ufoo,

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

#### 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.

#### Attachments

• 14.7 KB Views: 7

#### Ufoo

##### 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.
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

#### eibi

##### Active Member
Ufoo,

After too long to wait...see attached.

#### Attachments

• 14.8 KB Views: 6

#### Ufoo

##### Member
Ufoo,

After too long to wait...see attached.
Thanks a lot guru. The formula is working perfectly

#### Ufoo

##### Member
Ufoo,

After too long to wait...see attached.
Hello @eibi, can we tweak the formula to do a little bit more please?