Listing all possible combinations (with/without repetition) with a few conditions

[tonyleeocean]

Hi all -

There are 75 animals in my excel sheet, belonging to five species of animals (dogs, cats, pigs, rabbits and horses) respectively.

Each animal has 3 traits (1 major trait and 2 minor traits), and there are five types of traits in total for all animals (A, B, C, D, E).

Example as per below -

Animal	Species	Major trait	Minor trait 1	Minot trait 2
Katie	Dog	A	E	C
Bobby	Horse	B	C	D​

..... etc.

I would like to categorize any 48 out of the 75 animals into 16 teams, with each team having 4 animals (3 main animals and 1 supportive animal).

However, there are conditions as to the composition of animals in each team.

• Each team has 4 different species of animals
• The 4 animals in each team could only have 4 types of traits, e.g., ABC-ACD-BCD-ABD and none of the animals in this team have trait E
• The 3 main animals need to have 3 different major traits, e.g., none of the 3 main animals have same major trait of B, for example
• The main animals cannot be chosen again as main animals of other teams (hence 48 main animals to be chosen at the end)
• The supportive animals cannot be chosen again as supportive animals of other teams (hence 16 supportive animals to be chosen at the end)
• The 16 supportive animals chosen MUST be from the 48 main animals, i.e. all of the 16 supportive animals are also the main animals in the 16 teams)

The final output would be 48 animals (out of the 75) in 16 teams (3 main animals and 1 supportive animal for each team).

Would really appreciate any guidance or even a start! Let me know if I could provide any further clarity

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Listing all possible combinations (with/without repetition) with a few conditions
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[tonyleeocean]

Dear Fluff,

Please help delete my post above as I need to further refine my conditions a bit, which impact on the generation of combinations. Thanks so much

 

[Fluff]

No need to delete the thread, just post your requirements to this thread.