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:
<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:
<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!
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:
| Type | Date | Person |
| C | 1/4 | |
| A | 2/4 | |
| C | 2/4 | |
| A | 6/4 | |
| A | 8/4 | |
| B | 10/4 | |
| A | 12/4 | |
| B | 15/4 | |
| A | 16/4 | |
| A | 16/4 | |
| A | 17/4 | |
| B | 21/4 | |
| A | 25/4 | |
| C | 28/4 | a |
<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:
| Type | Date | Person |
| C | 1/4 | Z |
| A | 2/4 | X |
| C | 2/4 | Z |
| A | 6/4 | X |
| A | 8/4 | X |
| B | 10/4 | Y |
| A | 12/4 | X |
| B | 15/4 | Y |
| A | 16/4 | Y |
| A | 16/4 | X |
| A | 17/4 | X |
| B | 21/4 | Y |
| A | 25/4 | X |
| C | 28/4 | Z |
<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!
