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.

jmyzk_cloudsmart_jp

Hi @JD134

First, sort the 2,000 names and qualifications list based on their affiliated groups. Next, identify the position of each qualified individual within the 2,000 people for each qualification (Q#).

https://app.smartsheet.com/b/publish?EQBCT=01d0e2b0273341c9b63528609dce4743 (Link to the published demo sheet)

For example, for nurses, if there are 160 qualified individuals and Groups 1 and 2 need 11 nurses, and Group 3 needs 8, then you would assign Q#1-11 to Group 1, Q#12-22 to Group 2, and Q#23-31 to Group 3, and so on. Since the order after the sorting above keeps individuals close to those with the same affiliated group or nearby affiliated groups, this method helps achieve the goal of "keeping individuals from the same affiliated groups close to each other, preferably in the same group."

For example, in the case of nurses, Group 1 needs 11 nurses, but only 8 nurses belong to AG:01. Therefore, the remaining 3 nurses are supplemented by those belonging to AG:02 and AG:04. As seen in the example, nurses Q#1 to Q#8, belonging to the same AG, are assigned to three different groups. This ensures that the required numbers for each group are met while "keeping individuals from the same affiliated groups close to each other, preferably in the same group."

By comparing the qualifications required for each group with the total number of qualified individuals, we find that, on average, the same person needs to belong to 3.3 groups.

https://app.smartsheet.com/b/publish?EQBCT=2987f8b773c84b75b072765ed9a81321 (Link to the published demo sheet)

Cumulative Group Qualification Requirement: MOD

This calculates the cumulative number of each qualification needed for Groups 1, 2, 3, etc., and takes that number modulo the total number of qualified individuals. By determining the remainder with the MOD function using the number of qualified persons as a divisor, the cumulative required number of qualified persons can be repeated below the number of qualified persons, allowing the same person to belong to 3.3 groups, on average.

https://app.smartsheet.com/b/publish?EQBCT=bc6959fb19f748e8957946a029040c03 (Link to the published demo sheet)

[Qual1]=MOD(SUMIFS(INDEX({Group Qualification Assignment Range 1:10}, 0, 1), {Group Qualification Assignment Range : Row ID}, <=[Row ID]@row), INDEX({Number of Qualified and Required: Qualified}, 1))

For example, since there are 160 qualified nurses and a total need of 358, the numbers in the range [1 to 160] will repeat more than twice.

Looking at Groups 01-03, Group 1 nurses (or Qual7) requires 11 nurses (11 - 0), Group 2 needs another 11 (22 - 11), and Group 3 requires 8 (30 - 22). To refer to the cumulative number from the previous group, helper columns like Qual1 -1 and Qual2 -1 are added.

[Qual1−1]=IF([Row ID]@row = 1, 0, IF(INDEX([Qual1]:[Qual1], [Row ID]@row - 1) > [Qual1]@row, 0, INDEX([Qual1]:[Qual1], [Row ID]@row - 1)))

First two IFs deal with the beginning and end of the [Qual# -1]

A similar operation, as explained by the example, "Group 1 nurses (or Qual7) requires 11 nurses (11 - 0), Group 2 needs another 11 (22 - 11), and Group 3 requires 8 (30 - 22)," can be performed by the following formula:

[Groups] =JOIN(COLLECT({Cumulative Group Qualification Assignment : Group}, INDEX({Cumulative Group Qualification Assignment : 1:10}, 0, Index@row), [Q#]@row <= @cell, INDEX({Cumulative Group Qualification Requirement:1-10 -1}, 0, Index@row), [Q#]@row > @cell), CHAR(10))

This allows for determining which group a person should belong to.

Group Test

This is a sheet to verify which qualified members are selected in Groups 01-40. By comparing the numbers with the required qualification sheet per group and the qualification statistics sheet, it can be confirmed that the functions are working correctly.

https://app.smartsheet.com/b/publish?EQBCT=fb9d3574d1aa4a7aad28794ee9b582a5 (This published sheet is editable, so you can check by changing the Group dropdown value)

NicoLHC

@JD134 is everything resolved?