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

[SuperNerd]

I have 13 lists, with 4 values in each.

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!

 

[SuperNerd]

FYI- Not sure if it matters, but my actual data is transposed where I have 13 columns (one for each list) and 4 rows... again, probably doesn't matter, and I can transpose either direction to make it work for a solution...

 

[sandy666]

so which one you want to use for Cartesian?

Column1	Column2	Column3	Column4		Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
1a	1b	1c	1d		1a	2a	3a	4a	5a	6a	7a	8a	9a	10a	11a	12a	13a
2a	2b	2c	2d		1b	2b	3b	4b	5b	6b	7b	8b	9b	10b	11b	12b	13b
3a	3b	3c	3d		1c	2c	3c	4c	5c	6c	7c	8c	9c	10c	11c	12c	13c
4a	4b	4c	4d		1d	2d	3d	4d	5d	6d	7d	8d	9d	10d	11d	12d	13d
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

 

[SuperNerd]

Hi thanks for the reply. I’d prefer the 2nd/green one. I didn’t think it was a full Cartesian as all the examples of Cartesian have the same number of ‘choose’ as their are lists. Ie- since I have 13 lists, the Cartesian process would have 13 members in each set... and I need all combinations with 2 up to 5 members in each set.
Is there a way to do that?
Thank you!

 

[sandy666]

so you need something like this?
an example

1a
1a	2a
1a	2a	3a
1a	2a	3a	4a
1a	2a	3a	4a	5a

or post expected result. Use XL2BB to do that

 

[SuperNerd]

Yeah I guess. But within those requirements I posted. No repeats, none on the same row from the same column/list etc.
thank you!!!

 

[sandy666]

But within those requirements I posted

post expected result. Use XL2BB to do that

 

[SuperNerd]

Ok I’ll have to whip that up and post in the morning. I know there’s 1.4mil results tho so do you just want me to post some of each size result like in my example? Or a bunch of each one?

 

[sandy666]

would be fine with representative example, not over million

 

[SuperNerd]

haha! OK- how about something like this? Each variable within an individual cell... doesn't have to be in order.

sample.csv
	A	B	C	D	E
40	4b	13c
41	3c	5b
42	8a	11a
43	4a	6a	9b
44	4d	7d	12d
45	2b	6c	8a
46	12b	10c	4c
47	2a	4c	7d	5d
48	10a	4b	6d	1b
49	7a	13c	5c	11b
50	2a	8c	9c	13c	6d
51	1d	3d	8b	10b	11c
52	3a	5b	6c	10c	12b
IndCombos