+ Reply to Thread
Results 1 to 5 of 5

Assign staff into groups based on some criteria

  1. #1
    Registered User
    Join Date
    03-19-2020
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    2

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    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
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    03-19-2020
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    2

    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. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to assign case to staff fairly
    By sssyyy2002 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2020, 07:57 AM
  2. Replies: 8
    Last Post: 02-28-2018, 10:34 AM
  3. [SOLVED] Count the number of staff working between times based on staff position
    By sparky1978 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-03-2016, 08:10 AM
  4. [SOLVED] Ordering groups based on some criteria
    By Ucpaul in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2015, 02:19 PM
  5. Assign Employees to Training Groups evenly based on Manager
    By eoexcel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-16-2013, 05:18 PM
  6. Replies: 0
    Last Post: 10-03-2012, 11:17 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1