I have 13 lists, with 4 values in each.
I need to generate all possible combinations with some special requirements:
1) No value can repeat itself within a resulting combination set. (ie- combination result: {2a, 7b, 11d, 2a)- this is disallowed as '2a' is repeated)
2) No resulting combination set can have another value from it's own list (ie- combination result: {4c, 6d, 9a, 9c, 13a} - this is disallowed as 9a and 9c are from the same list)
3) I need to be able to choose all combinations in resulting sets of 2, 3, 4 and 5 values. (examples- {1c, 9b}, {2a, 7c, 8d}, {4a, 5c, 11b, 13d}, {7a, 8a, 10c, 11b, 12a}
I realize this results in 1+MIL results. It's a pretty complex workbook we're generating.
I've spent nearly a week full time searching and searching and not coming up with a way to do this. I'm sure Power Query is the best way, but am wide open to any method that gets me the results. I'd appreciate any help you can give- the boss is putting pressure on me and I'm really stuck!
Thank you!
VBA Code:
1a 1b 1c 1d
2a 2b 2c 2d
3a 3b 3c 3d
4a 4b 4c 4d
5a 5b 5c 5d
6a 6b 6c 6d
7a 7b 7c 7d
8a 8b 8c 8d
9a 9b 9c 9d
10a 10b 10c 10d
11a 11b 11c 11d
12a 12b 12c 12d
13a 13b 13c 13d
I need to generate all possible combinations with some special requirements:
1) No value can repeat itself within a resulting combination set. (ie- combination result: {2a, 7b, 11d, 2a)- this is disallowed as '2a' is repeated)
2) No resulting combination set can have another value from it's own list (ie- combination result: {4c, 6d, 9a, 9c, 13a} - this is disallowed as 9a and 9c are from the same list)
3) I need to be able to choose all combinations in resulting sets of 2, 3, 4 and 5 values. (examples- {1c, 9b}, {2a, 7c, 8d}, {4a, 5c, 11b, 13d}, {7a, 8a, 10c, 11b, 12a}
I realize this results in 1+MIL results. It's a pretty complex workbook we're generating.
I've spent nearly a week full time searching and searching and not coming up with a way to do this. I'm sure Power Query is the best way, but am wide open to any method that gets me the results. I'd appreciate any help you can give- the boss is putting pressure on me and I'm really stuck!
Thank you!