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