Distribute assignments evenly among employees based on varied weighting of assignments...?

Maybe someone can figure out a better way to do this perhaps with Solver.

I can’t get Excel to do it for you, but I can get Excel to help. Excel can decide what work needs to be done and who is left to do it. As for balancing the work, it can help.

I have a strong liking for Tables in Excel, so I converted your range of data on the Client Alpha Distribution to a tables. Tables know how big they are so you don’t have to change formulas and you can reference columns by column header names. Tables also copy formulas down automatically.

On the first sheet I have three pivot tables. These are overlaid with named dynamic ranges: List1, List2 and List3.

List1, the first pivot table gives a unique listing of names. This range is used to validate a dropdown list in Cell I1.

When you select a value in I1, it sets a True / False in Column D (Available1). The value in column D is used to filter List2. This is a list of all unique names of people who are left.

This range is validated for the dropdown in Cell I3. This sets a True /False in Column E. Column F is a list of all people left after the two people are selected. This list is used later on.

If you wish, you can hide columns D:F and the columns containing the pivot tables.

This brings us to the balance sheet.

Cell B1 has the number of hours to cover. (I know that they are percents, but I thought of them as hours and that’s the terminology I used).

Cell B3 is the number of people left to cover these hours.

Cell B5 is the target number of hours per person. This figure is what you are trying to hit in column P.

Cell H1 has the number of hours covered by the assignment. Column J1 shows the number of hours that still need to be covered.

Columns D:J are part pivot table / part assignment. You will be doing data entry in only Columns I and J. The shaded blue section is what’s “active.” It’s just a conditional format to keep your focus on the relevant data.

Column F has the formula: =COUNTA(I4:K4). If you have a name only in column I, this is 1. If you also have a name in column J, then this is 2.

Column G has the formula =IFERROR(E4/F4,0). This formula calculates the per person contribution to the task.

Column H has the formula: =F4*G4. This formula calculates the total coverage for the task, It should wind up the same as column E.

Now you start the balancing act.

Enter a name in Column I and Column H. These are validated from List3 (who's left) and you can delete data in a cell after you make a selection. In the example shown, Cindy and Jerry share task M. They also share task V. The tasks are ordered in descending order by percent total. This will help decide how to balance. If someone on the top of the list needs to take more workload, then they should be assigned a task towards the bottom of the list.

As you fill in the names, the information in Columns M:P changes showing the current workload in column P.

I hope this helps - good luck!

I've uploaded the file with my solver model. In order to test you may need to add solver as it's not installed by default. If solver is installed you will find the solver icon under the data tab.

To do a rerun clear range D4:H10, click Solver icon (data tab) and click "Solve" button.

Alf

If you are talking about my solution, then I'd need to extend everything out by a column an give you another column on the Balance Sheet - a modest amount of work.

Your late response is not an issue. I just got back from a mini-vacation. Today is catch up day. It looks like I'll have to go back and "re-discover" the requirement.