Complex List Generator- Multiple Lists, Multiple Choose N's, Millions of Results

SuperNerd

New Member
Joined
Sep 16, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
I have 13 lists, with 4 values in each.

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!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
OK SERIOUSLY! I CANNOT thank you enough! You're a life saver! I sincerely appreciate it! There's no way I would've figured this out on my own... THANK YOU!!!!
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top