How to assign people to groups with certain constraints

[maria.elisa]

Hello everybody,

I am wondering if someone can help me to create in Excel a system to assign people in groups. The problem is that the members have different nationality and there cannot be two people from the same country in the same group. I was thinking about using solver, but I am not sure how to built the nationality constraint.

Thank you!

[6StringJazzer]

You will have to provide more information. Ideally you would provide an Excel file that includes this information.

List of people and their nationalities. Avoid private data, this can be fake names.
List of groups you want to assign them to.
Rules about how many people can be in a group.

A "before" and "after" view of how you picture that you want this to work.

See yellow banner at the top of the page.

[Alf]

A possible solver setup could look like this. Total participants from each nation specified in range J5:J9.

As the Excel solver is limited to max 200 constraint you may run into this limitation with a high number of people / nations. If so try the freebie OpenSolver.

https://opensolver.org/

Alf

[Petkov]

I am using similar model for another purpose, and it just works fine. The problem is that you get the same result each time, even though there are multiple combinations to arrange the binary values, and still satisfy the constraints. Even if you set all bin values initially to zero, that is before you start solver, you still get the same values as the last time. Is there a way to make solver to generate random different combinations that satisfy the constraints, each time? Because, in general, you might want to use this model many times, and to get different combinations every time.

[6StringJazzer]

I am using similar model....Is there a way to make solver to generate random different combinations that satisfy the constraints, each time?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread