Assigning tasks to team members based on a few criteria

[enguehard]

Hi everyone,

I am attempting to optimize a tool that assists in assigning team members to certain tasks, based on their available capacity (work hours). The idea is that the team members (coaches) will be coaching other players on five types of tasks, each taking a different amount of time. Ideally, each player should be assigned the same coach for all five tasks, if that coach has enough available time.

I have a table with a column of coach names, along with columns of location, available hours and assigned hours. The available hours are a fixed number, while the assigned hours should be a running total.

For example:

Coach	Location	Hours available	Hours assigned
Phil	Downtown	5.0	0.0
Jenny	Uptown	3.0	0.0
Frank	Uptown	12.0	0.0

I have a second table with the names of the player being coached, their location, along with the five tasks they are being coached on and the time it takes for each one.

What I want to do is set it up so that excel can automatically choose coaches from the table above, matching coach and player location, and populate the second table, without exceeding the capacity of the coach above. If the coach is already at capacity, or if the next tasks in the line will cause them to exceed capacity, it skips them and selects another coach.

Player	Location	Task 1	Coach	Task 2	Coach	Task 3	Coach	Task 4	Coach	Task 5	Coach
Jim	Downtown	0.7	Blank	0.3	Blank	0.5	Blank	0.8	Blank	1.0	Blank
Ellie	Uptown	0.7	Blank	0.3	Blank	0.5	Blank	0.8	Blank	1.0	Blank

Again, the idea is that this assignment should be automatic and not user driven, because we want the matching to be impartial.

I know this seems like a tall order, but I'm really hoping someone will be able to help me figure it out.

[AliGW]

Welcome to the forum! Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[enguehard]

Here is the workbook attached. The ideal state tab represents how I intend the final product to look, however I entered the assignments manually obviously. I did not take the location into consideration when making the mockup, but the excel version should only assign coaches with players in the same location.

Ideally, as I said above, I would like excel to assign the coaches automatically, maybe with a click or something like that.

Thank you for looking at this and any help you can provide!

[AliGW]

I did not take the location into consideration when making the mockup, ...

This means you are likely to end up with a solution that doesn't work with your real data. Please take a moment to provide a mock-up of what you really want. Thanks!

[enguehard]

Understood. I've made the necessary correction.

[JeteMc]

This is given in the spirit of 'any help', as I realize that it would require some changes in layout and require translation of the output.
Sheet 1 of the attached file contains a table populated by the formula:

Please Login or Register  to view this content.

A conditional formatting rule hides zeros that occur.
The coaches may be sorted by location and then by hours available, either smallest to largest to involve the most coaches, or largest to smallest to have the greatest number of players work with only one coach.
Assignments of 3.3 hours would mean that the coach would coach all five tasks whereas other values represent combinations of a fewer number of tasks.
Let us know if you have any questions.