Hey all,
I am currently learning VBA and having a hard time to figure out how to put this into Code. The Forum helped me a lot during my first steps in VBA and I already learned a lot but this one seems a little more individual as I did not find anything on the web which helped me.
First I would like to describe the situation. There are items already grouped which should be put on a machine. Not each productgroup can be produced on each machine. I have created a matrix which will explain this a little better with some values in it.
free image hosting
I am already stuck with the sorting of the groups. First I need to delete all production groups that do not have values (so they will not be produced).
The result should look like this:
I converted in the matrix from [hh]:mm to "days" because I think in code it is better to calculate with.
Then the productiongroups should be sorted by 3 criterias:
- First sort by the earlierst delivery date and
-if there a more than one group with the same delivery date, then sort it by the duration of the production.
-If the duration is also the same just take the lowest product group number.
I really do not know how do code this cascaded filtering in VBA. Even searching for hours did not get me any further.
This should then look like this ( I hope my writing there makes any sense):
PG = productgroup
{(Machine, production time)}
However, if then the listing is sorted I need to figure out which product group should be produced on which machine. Once a production has started I can not interrupt the Job, so it is a 0/1 problem.
In the example in the picture above I have 432 possible combinations for this. The number of combinations rapidly goes up if I add some other productgroups to this. The maximum by now is something about 17.000.000. This then will be a huge amount of calculations.
The production capacity of the machines is limited to the latest delivery date and the goal of this is to have the shortest possible production time.
The result then should be something like this:
image hosting
As soon as a solution is found which is as long as the production time for the longest single productgroup, the program can stop, as it will not be any shorter. If the results in the end show multiple solutions with the same time the program should just choose the first one found with this production time (basically it does not matter which one).
I hope you can help me with this issue by having any links, search suggestions or code to bring a little light in my project.
I am currently learning VBA and having a hard time to figure out how to put this into Code. The Forum helped me a lot during my first steps in VBA and I already learned a lot but this one seems a little more individual as I did not find anything on the web which helped me.
First I would like to describe the situation. There are items already grouped which should be put on a machine. Not each productgroup can be produced on each machine. I have created a matrix which will explain this a little better with some values in it.
free image hosting
I am already stuck with the sorting of the groups. First I need to delete all production groups that do not have values (so they will not be produced).
The result should look like this:
I converted in the matrix from [hh]:mm to "days" because I think in code it is better to calculate with.
Then the productiongroups should be sorted by 3 criterias:
- First sort by the earlierst delivery date and
-if there a more than one group with the same delivery date, then sort it by the duration of the production.
-If the duration is also the same just take the lowest product group number.
I really do not know how do code this cascaded filtering in VBA. Even searching for hours did not get me any further.
This should then look like this ( I hope my writing there makes any sense):
PG = productgroup
{(Machine, production time)}
However, if then the listing is sorted I need to figure out which product group should be produced on which machine. Once a production has started I can not interrupt the Job, so it is a 0/1 problem.
In the example in the picture above I have 432 possible combinations for this. The number of combinations rapidly goes up if I add some other productgroups to this. The maximum by now is something about 17.000.000. This then will be a huge amount of calculations.
The production capacity of the machines is limited to the latest delivery date and the goal of this is to have the shortest possible production time.
The result then should be something like this:
image hosting
As soon as a solution is found which is as long as the production time for the longest single productgroup, the program can stop, as it will not be any shorter. If the results in the end show multiple solutions with the same time the program should just choose the first one found with this production time (basically it does not matter which one).
I hope you can help me with this issue by having any links, search suggestions or code to bring a little light in my project.
