Dear community, good morning.
I am new to this forum and hope you can help me.
Here is my problem.
Short version:
1. I need VBA to generate all possible combinations following a certain logic
2. Once a new combination is found, this combination needs to be evaluated with a certain function (that I already have)
Detailed version:
- I need to optimize the allocation of products to a shelf
- I need only two integer variables, number of products N and shelf-space (capacity) S
- I want VBA to generate the full list of all possible combinations of allocating products to the shelf and evaluate each combination with a certain function (that I already have)
- Assumption 1: I always need to fill the shelf completely
- Assumption 2: the order of the products does not matter (means only the total number per product counts, not where the product is placed on the shelf)
- here comes an example for N=3 product and S=3 shelf-spaces (which means that I can put a total of 3 products onto the shelf) - which gives in total 10 possible combinations
Quantity of product 1 Quantity of product 2 Quantity of product 3
0 0 3
0 1 2
0 2 1
0 3 0
1 0 2
1 1 1
1 2 0
2 0 1
2 1 0
3 0 0
I assume you need to build a kind of "recursive" loop for this. I have managed to write a non-flexible macro which can deal with a flexible number for S but I always need to build in a new loop whenever I want to add a product.
What I am looking for is a fully flexible macro such that I can just put S and N into two excel cells and the macro evaluates all combinations.
You do not need to worry about the evaluation function for each combination (to simplify, assume this is just the sum of the number of all products, which then of course would always equal S).
Please do let me know if you need more information - I hope the above is clear.
Thanks a lot in advance for your help.
Best regards
Kai
I am new to this forum and hope you can help me.
Here is my problem.
Short version:
1. I need VBA to generate all possible combinations following a certain logic
2. Once a new combination is found, this combination needs to be evaluated with a certain function (that I already have)
Detailed version:
- I need to optimize the allocation of products to a shelf
- I need only two integer variables, number of products N and shelf-space (capacity) S
- I want VBA to generate the full list of all possible combinations of allocating products to the shelf and evaluate each combination with a certain function (that I already have)
- Assumption 1: I always need to fill the shelf completely
- Assumption 2: the order of the products does not matter (means only the total number per product counts, not where the product is placed on the shelf)
- here comes an example for N=3 product and S=3 shelf-spaces (which means that I can put a total of 3 products onto the shelf) - which gives in total 10 possible combinations
Quantity of product 1 Quantity of product 2 Quantity of product 3
0 0 3
0 1 2
0 2 1
0 3 0
1 0 2
1 1 1
1 2 0
2 0 1
2 1 0
3 0 0
I assume you need to build a kind of "recursive" loop for this. I have managed to write a non-flexible macro which can deal with a flexible number for S but I always need to build in a new loop whenever I want to add a product.
What I am looking for is a fully flexible macro such that I can just put S and N into two excel cells and the macro evaluates all combinations.
You do not need to worry about the evaluation function for each combination (to simplify, assume this is just the sum of the number of all products, which then of course would always equal S).
Please do let me know if you need more information - I hope the above is clear.
Thanks a lot in advance for your help.
Best regards
Kai
