# Assign staff into groups based on some criteria

1. ## Assign staff into groups based on some criteria

Good morning, I need some help here to get started. I have 3 workshop rooms to allocate staff evenly based on criteria such that it balances out the 'smart ones' (column F, Score), gender, country, agency and seniority (column E, title). The order of the criteria is in descending level of priority. In other words, the most important criteria is to ensure the average score in group 1 is comparable to group 2 and 3, then try to balance out the second, third, forth and fifth criteria. One thing to note is that we usually have around 27-31 staff per seminar, so the number of staff is not fixed.

Appreciate if anyone could come up with solution to 'automate' the allocation either through formula or macros

My initial approach is to randomly assign the staff, then 'sumif' for each of the 3 groups to check the average score, 'countif' for gender count, country count, agency count, and seniority count. I will press F9 until I observe a well balanced distribution in the 5 criteria. Having said that, I am sure there is a better way of doing this with some 'rules'. Appreciate your kind help.

2. ## Re: Assign staff into groups based on some criteria

Hey Pat,

I'd rank them a sort and then put them in groups. See if a Pivot Table makes sense to examine country and other... See the attached.
Three Mixed Groups.xlsx
If you don't like groupings then change a group number or two in the left table and refresh the pivot to see how the result is.

Hope this helps. I don't think there is a "best" answer.

3. ## Re: Assign staff into groups based on some criteria

Here's a simple solution

Select your data and Sort them based on your criteria where Score has priority, then gender, then country etc. Note: with a list this small and so few tied scores, the other criteria have very little impact.

Then assign a room to the sorted list going down using a pattern like...

1
2
3
3
2
1
1
2
3
etc

This should evenly distribute the "smart" ones and to a lesser degree the other criteria.

I got and average Score for each room almost identical

Rm. - Avg Score
1 - 0.59
2 - 0.59
3 - 0.586

4. ## Re: Assign staff into groups based on some criteria

Thank you MarvinP and AlphaFrog, that was really quick work there. I agree there is no 'best' way to allocate.

AlphaFrog - sorting the scores and assigning 123,321 helps to balance out the scores in itself.

MarvinP - when you said change the group number, do you mean swop the staff around? If yes, isn't this random iteration?

5. ## Re: Assign staff into groups based on some criteria

Hi Pat,

I've done this problem for golf groupings. Each team would get 4 members and each player has a golf index from 0 to 30 (or so). The job is to distribute somewhat equitably the teams. What Alpha Frog suggests, we call a snake grouping. The real problem is what is your goal. Should gender count more than country or position be more important than company? What is important is that you set the rules before making the groups and tell everyone how you did it. That way you aren't accused of favoritism.

When I say change the groups, I was meaning to overwrite the Mod() formula with real numbers and then Refresh the pivot table. This would show what effect a change or two might have.

There are currently 1 users browsing this thread. (0 members and 1 guests)