Work Allocation Across Dates

Enginair

New Member
Joined
Mar 13, 2019
Messages
1
Hello,

I've got a task given to me that's been quite manual in the past that I'm trying to speed up with the help of some vba/formulas.

Basically I've got a list of products (A,B,C) with a manufacture due date that need to be allocated between 3 different people (X,Y,Z).
The dates will differ every month but they are fixed.

Product A can only be made by Person X or Y
Product B can only be made by Person Y
Product C can only be made by Person Z

Person X must manufacture exactly 6 of product A in April, exactly 7 in May, exactly 6 in June and so on throughout the year..
There will always be at enough of product A in any given month that can be allocated to Person X.

An example of what the raw data would look like before work is allocated:

TypeDatePerson
C1/4
A2/4
C2/4
A6/4
A8/4
B10/4
A12/4
B15/4
A16/4
A16/4
A17/4
B21/4
A25/4
C28/4a

<tbody>
</tbody>


Additionally as much as possible the work for Person X must be spread out over the month. For example using the table above instead of allocating 3x Product A over 16/4, 16/4 and 17/4 to Person X they would be allocated 16/4, 17/4 and 25/4. It doesn't matter how the work is allocated for Persons Y and Z.

An Example of what the finished data would look like:

TypeDatePerson
C1/4Z
A2/4X
C2/4Z
A6/4X
A8/4X
B10/4Y
A12/4X
B15/4Y
A16/4Y
A16/4X
A17/4X
B21/4Y
A25/4X
C28/4Z

<tbody>
</tbody>


I hope I've explained it well enough.. I'm really not sure how to begin on this one, do you have any ideas how I could do this? My Googling/searching of this forum hasn't lead me to anything yet.

Any advice and help would be greatly appreciated!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Watch MrExcel Video

Forum statistics

Threads
1,109,049
Messages
5,526,488
Members
409,703
Latest member
nbkqsj7

This Week's Hot Topics

Top