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.
Bookmarks