help with a vba to generate combinations-permutations

[thoandros]

Dear all
I am trying to create a vba code to generate all possible permutations that respect some criteria.

At the moment I am achieving acceptable results with a complex huge matrix full of vlookups, index and countifs... the file is too big and the calculation too slow. So I was wondering to do the same using a vba.

I hope to be able to be as clear as possible:


I have 6 groups made of 3 columns. Group 1 is columns ABC, Group 2 DEF etc etc.

For each group I know: values that are FOR sure in the group and values that COULD be in the group.

Values are numbers 1 to 18 and they cannot repeat.

Let's say, if I know that group 1 has FOR SURE values 2 & 8 and can have values 9, 10, 15 the results of the vba should be

2, 8, 9
2, 8, 10
2, 8, 15



Another example:
I know that group 1 has for sure value 2 and could have values 1 and 3 and 5
I know that group 2 has for sure value 7 and 8 and could have values 1 and 3


because a value cannot repeat, the result should be

GROUP 1 // GROUP 2
2, 1, 5 // 7, 8, 3
2, 3, 5 // 7, 8, 1

note: the order of the values within the group is not relevant. meaning that I don't need the macro to return all permutations of values within the same group.


Let's say that I have 3 sheets:
in Sheet1 I have for each column from A to F (representing Group 1 to 6) the values that ARE surely within the group
in Sheet2 I have for each column from A to F (representing Group 1 to 6) the values that COULD BE within the group

in Sheet3 the vba should return per each row any possible combination, with a value per cell (so 18 columns wide)


I forgot to mention that there would never be a case where I have all 18 values in the "could be" groups, because I know that would lead to millions of combinations. Most likely there will be always at least 5 or 6 values marked to be for sure in a group and the values that could be in a group are never many, narrowing this way down the outcome.


I really hope someone can help me with this, thank you all in advance and if something is unclear just let me know

[mikerickson]

What about the case where you have more "for sure" than the number of characters.
What result do you want for

For sure: 1,2,3,4
Might be: 5,6

[thoandros]

Hi,
no this cannot be. the for sure is max with the 3 values and if that is the case, then there will be no values in the "might be" for that group cause the 3 values are defined. hope it makes sense.

If you want, here a "live" example

For sure in the groups
1) 1,3
2) 10,14
3) 13,17
4) 4,6
5) 7,8
6) 2,11

Could be in the groups:
1) 5,9,12,16,18
2) 5,9,12,16,18
3) 9,16,18
4) 9,15,16,18
5) 5,12
6) 12,15,16,18


thank you!

[mikerickson]

You don't mention where these must have or might bes are or where you want the output

This routine creates a fixed delimited string from the Must Haves
Then calculates how many of the Might Be need to be added to that fixed string to get the total number of numbers correct
Then creates all the possible strings (without duplication, not order dependent) from the Might Be's and adds them to the fixed string

Please Login or Register  to view this content.

As I said, the input and output need to be tweeked for your situation. (Current output is a series of message boxes, press NO to stop the process)

[thoandros]

hello! Thanks i am going now to test that.
In regards to where the inputs are, I actually mentioned it but maybe it was confusing:

Let's say that I have 3 sheets:
in Sheet1 I have for each column from A to F (representing Group 1 to 6) the values that ARE surely within the group.
like cells A1 & A2 & A3 contain the MUST be for Group 1, etc

in Sheet2 I have for each column from A to F (representing Group 1 to 6) the values that COULD BE within the group

in Sheet3 the vba should return per each row any possible combination, with a value per cell (so 18 columns wide)


hope this helps?

[thoandros]

I tried the code but I am confused with the message boxes. I get 1,2,3 then 1,2,4 and then 1,2,5 but I don't see the results. Can you have the macro write the various strings in the cells of Sheet3? I can use Text to column funciton then to explode it cell by cell.
thank you!

[mikerickson]

Can you post a workbook with an example of your inputs and the desired outputs.

[thoandros]

this is pretty much what I would like to achieve, of course in the sheet RESULTS I just completed 3 lines but there should be quite few more

thank you

[mikerickson]

Try this file

[thoandros]

thank you very much.
For what I see, this macro creates, per group, each possible set of 3 values, but it does not create all the possible combinations...
in theory the ultimate goal is that it combines each of the columns but exclude those where a value is present in more than a group. Does it make sense?

Now, starting from what you did, I am able to use a vba that combines the value of 6 different columns and creates all the possible combinations.

See the file attached. Go into RESULTS 2.

You see that the first 6 columns are the results as done by your macro (ideally, can you make it to be this way?)

Columns with the yellow header are the output of macro "combinations".

Columns with header in blue is a concatenate formula and in pink it's the concatenate "exploded" using text to column.


Now, the final output should actually be what you see in the columns N:AE but excluding all rows where a value is repeated, as this cannot happen.

Are you able to make the macro do that? I can't think at a code to do that, only using countifs etc

Let me know if it's all clear and thank you for all the help

[thoandros]

a quick one
I tried to modify my original file, where the data is coming from to run the macro. I have modified the Sheet numbers within the Code etc but I always get
Runtime error 9
Subscript out of range
and when I click on debug it highlights this

Please Login or Register  to view this content.

I really don't understand what is wrong. I copy paste the values into the test2 file and it works perfectly. So I don't get what is wrong, could it be because there are many sheets in the original file?

[thoandros]

Here another issue I found. I forgot to mention that there might be the case where for a group there is nothing in MUST (whilst if there is nothing in MIGHT it means the set of 3 is the one in MUST).
The macro does not work. See the file, in theory for all those where there is nothing in MUST, the macro should return all combinations of 3 with elements from might.


How can we do that?

[thoandros]

please ignore my last 3 messages. I sorted that out. thank you very much for your help with this.