Assigning Individuals into groups based on qualification and group

JD134

I have a bulk list of 2000+ names along with their certification/qualification and affiliated group.

I need to come up with an automated way of assigning each of these people into 40 groups. Each group must have a minimum of X numbers of each qualification (ex. Group 1 must have a 2 plumbers, 2 scientists and 10 fisherman, where group 2 must have 40 plumbers, 8 scientists and 2 fisherman). I'd prefer to keep any people with affiliated groups close to each other, preferably in the same group.

Brian_Richardson

The simplest approach is a little bit manual, but should be easy to read and understand to make your "human brain" decisions on assignment.

In one sheet add 40 rows, each row has a Group Name in the Primary column. Add a column next to that called "Role" with the dropdown options for the role assigned to each person. Leave these cells blank for your groups. Now add the columns shown below with column formulas (type in the formula in one cell in the column, then right click and choose Convert to Column Formula)

• "Group Assignment" column with the formula =PARENT(Primary@row)
• "Total Members" column with the formula =COUNT(CHILDREN(Primary@row))
• "Total Scientists" column with the formula =COUNTIF(CHILDREN(Role@row),"Scientist")
• repeat the columns and formula for each role
• # Scientists Needed", populate it with 40 or whatever total count you need for each group, repeat for each role.
• A checkbox column "Scientists Need Met?" with the formula =if([Total Scientists]@row>=[# Scientists Needed]@row,true). Repeat for each role.

Now make a 41st row called Unassigned group

Then add your 2,000 names, with their data, under the Unassigned group name and indent them one step. That will make your people the "children" of the Unassigned group. You'll see that the Group Assignment column should say Unassigned for all of them and the Total Scientists, Total Plumbers, etc columns should count up the number of roles that you have in total.

Finally, it's time for the grouping! Do a simple drag-drop of your rows to move people into the groups that they are getting assigned to. You can left click grab the row number and drag it. When you drag a person under the group they are in, they should be indented under that group already, but if not then indent them one notch. You should see the Total xxxx role column count increase by 1, and if you have dragged enough of that role into your group, the Role xxx Needs Met checkbox should check itself.

You can make this even a little easier by adding Conditional Formatting rules that say "If Scientist Need Met checkbox is unchecked, highlight the # of Scientists cell in red" for a visual approach.