Create a filling schedule on a fill line that completes the batches in the shortest time

VBA model question!

File Home Insert Draw Page Layout Formulas Data Review Calibri 11 M12 fx B 1 Create a filling schedule on a fill line that completes the batches in the shortest time 2 3 Changeovers 4 Same Type Batch Changeover: 4 hrs 5 Different Type Batch Changeover: 8 hrs 6 Reclean Changeover: 24 hrs 7 8 Clean Expire Time: 120 hrs 9 10 Filling Rate: 332 vials per minute 11 The filling line must be cleaned before use. This process takes 24 hours (including the setup for the first batch and the breakdown for the last batch). After the clean, it can be used for multiple batches over the course of 120 hours before needing to 12 be recleaned A lot must be completely filled consecutively (i.e. it cannot be broken up into pieces 13 or distributed across a line reclean) Lots of the same type can be changed over in 4 hours, while lots of a different type 14 can be changed over in 8 hours 15 16 Create a tool that can generate a filling schedule that follows these rules with an effort towards completing the batches in the shortest time. The output doesn't need to be 100% optimal, but it must use some heuristics to reduce changeover 17 time over simply running in order 18 The example data in the "Example Lots" sheet can be used to generate an output, but the tool should be able to handle all potential data in this format. The schedule 19 should reorder these lots to complete the whole set in the least amount of time 20 21 The output should provide an actionable schedule for the manufacturing team File Home Insert Draw Page Layout Formulas Data Review Calibri 11 M12 fx B 1 Create a filling schedule on a fill line that completes the batches in the shortest time 2 3 Changeovers 4 Same Type Batch Changeover: 4 hrs 5 Different Type Batch Changeover: 8 hrs 6 Reclean Changeover: 24 hrs 7 8 Clean Expire Time: 120 hrs 9 10 Filling Rate: 332 vials per minute 11 The filling line must be cleaned before use. This process takes 24 hours (including the setup for the first batch and the breakdown for the last batch). After the clean, it can be used for multiple batches over the course of 120 hours before needing to 12 be recleaned A lot must be completely filled consecutively (i.e. it cannot be broken up into pieces 13 or distributed across a line reclean) Lots of the same type can be changed over in 4 hours, while lots of a different type 14 can be changed over in 8 hours 15 16 Create a tool that can generate a filling schedule that follows these rules with an effort towards completing the batches in the shortest time. The output doesn't need to be 100% optimal, but it must use some heuristics to reduce changeover 17 time over simply running in order 18 The example data in the "Example Lots" sheet can be used to generate an output, but the tool should be able to handle all potential data in this format. The schedule 19 should reorder these lots to complete the whole set in the least amount of time 20 21 The output should provide an actionable schedule for the manufacturing team