Generating all possible combinations per macro

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

Hi Kani2015,

Please use the below code to generate your permutations

Please Login or Register  to view this content.

Regards,
lokicl

Hello Lokicl,

thanks for looking into that and sending me the code.

I tried it and I see what this is doing. I think it is giving me the permutations - which will consider the order of products. The order in my case does not matter. I would need the combinations only.

Also, I do not see how you can flexibly define N and S ... Does this make sense?

In the meantime I found out one more thing: the total number of combinations you get with N and S is (N+S-1)! / (S!*(N-1)!).

Perhaps that helps. Do you (or anyone else) have a different idea?

Thanks again.
Best regards

Kai

Hi Rioran,

thank you! Yes - xls will not have enough rows due to the combinatorical "explosion" by the above term (N+S-1)! / (S!*(N-1)!).

I will post a short version of my non-flexible code below. Hope that helps.

Have a good evening
Kai

Hi Rioran,

any chance you could progress on this topic?

Thanks and cheers
Kai

Hi shg,

yes - choosing N=S=3 was perhaps not the best example to illustrate it here.

The 3 digits do NOT represent the shelf spaces/slots but the numbers of each product.

Example 1:
0 3 0 means product 1 is not listed (0), product 2 is listed with 3 items (3) and product 3 again is not listed. Thus, product 2 consumes the total shelf-space (S=3) on its own.

Example 2:
0 0 3 means product 1 is not listed (0), product 2 is not listed either (0) and product 3 is listed with 3 items. Thus, product 3 consumes the total shelf-space (S=3) on its own.

I am not interested in where the products are placed (left, middle or right slot of the S=3 spaces). I am just interested in how often the single products appear on the shelf.



Cheers and good night
Kai

Hi shg,

I think I would need to do this for up to N=15 products and shelf-space of S=50.

I know that this macro will potentially run for hours.

I am building a non-linear model for this shelf-space problem in GAMS and I use a standard solver. Therefore I need to show (through comparison to full enumeration by generating all combinations, exactly what I want to do with the VBA code) that the standard solver yields good results.

Best regards
Kai

Good morning Shg - I will take a look at this!

Thanks a lot for now - this looks very helpful. I will get back here after reviewing and testing the code!

Have a good day!
Kai

Hi - I did a quick check already.

First of all - thanks again. This is going into the right direction I think.

However, I am not sure that the total number of combinations is correct. E.g. with N=4 and S = 3, I know that this should be 20 combinations in total. The code is giving me 15 only - so some are obviously skipped.

Do you see what I mean?

Thanks
Kai

Without fully understanding the code yet - I think you need to get rid of the -1 in the main sub.

Please Login or Register  to view this content.

At least I get now the right number of combinations. Will investigate further. Cheers

Here is my manual enumeration for N=4 products and S=3.

Read the table as follows: 4 columns because we have 4 products. Different combinations in rows. First number in a row = number of items of product 1 put to the shelf, second number in a row = number of items of product 2 put to the shelf.

Row sum is always 3 as shelf-space is limited to S = 3.

1	2	3	4
0	0	0	3
0	0	1	1
0	0	2	1
0	1	1	1
0	1	2	0
0	1	0	2
0	0	3	0
0	3	0	0
0	2	0	1
0	2	1	0
1	0	0	2
1	0	2	0
1	2	0	0
1	0	1	1
1	1	0	1
1	1	1	0
2	0	0	1
2	0	1	0
2	1	0	0
3	0	0	0

Ok. Will try - that means exchanging just nProd and nShlf in the sub main()? Or do I need to do more?

Please Login or Register  to view this content.

Will look into that tomorrow again - so will not reply today anymore.

Anyhow - thanks again for your support.