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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,279
Members
430,201
Latest member
Deepakpilla36

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top