Work Allocation Across Dates

[Enginair]

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:

[TABLE="width: 300"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Person[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]6/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]8/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]10/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]15/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]16/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]16/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]17/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]21/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]25/4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]28/4[/TD]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 300"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Person[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1/4[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2/4[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2/4[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]6/4[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]8/4[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]10/4[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12/4[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]15/4[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]16/4[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]16/4[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]17/4[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]21/4[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]25/4[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]28/4[/TD]
[TD]Z[/TD]
[/TR]
</tbody>[/TABLE]


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!