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]

Weird- that XL2BB did strange things with the formatting. Let me know if this works of it you need anything else. THANK YOU SOO MUCH!!!

 

[sandy666]

Each variable within an individual cell... doesn't have to be in order.

CHAOS!

I'll try this in my spare time but I can't promise anything
you can use vba random without repetitions but it's not my story
maybe someone else will be quicker and give you solution for that

 

[SuperNerd]

It can be in order, too! It really doesn't matter as long as I get all the combinations! LOL... Thank you!

 

[SuperNerd]

Or, it can be all in the same cell and I can split the cells later... whatever is easiest! THANK YOU!

 

[Eric W]

I've written macros to create combinations like this before, and I've yet to see any valid reason for enumerating every combination. There are just too many to do anything useful with. This particular problem will result in 1,520,480 combinations. If a person were to manually scan the list, taking 1 second per combination, it would take nearly 3 weeks to do so, with no sleep, eating, or anything else. And if you're going to have another program evaluate the combinations, looking for some specific criteria, it's better to do so while the combinations are being created, without having to display them.

Nevertheless, since everybody still wants to print them out, here's a macro you can try. Open a new workbook, with 2 sheets called Sheet1 and Sheet2 in it. Put your values in Sheet1!A1:M4 as shown:

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1a	2a	3a	4a	5a	6a	7a	8a	9a	10a	11a	12a	13a
2	1b	2b	3b	4b	5b	6b	7b	8b	9b	10b	11b	12b	13b
3	1c	2c	3c	4c	5c	6c	7c	8c	9c	10c	11c	12c	13c
4	1d	2d	3d	4d	5d	6d	7d	8d	9d	10d	11d	12d	13d
Sheet1

Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Copy this code to the sheet that opens:

Option Explicit
Public d1 As Object

Sub StartProc()
Dim MyData(1 To 52, 1 To 2), ResTab()
Dim c As Variant, i As Long, k As Variant, k1 As Variant

    For Each c In Sheets("Sheet1").Range("A1:M4")
        i = i + 1
        MyData(i, 1) = c.Value
        MyData(i, 2) = c.Column
    Next c
    
    Set d1 = CreateObject("Scripting.Dictionary")
    
    For i = 2 To 4
        Call BuildCombo(i, MyData, 0, 0, "", "")
    Next i
    
    ReDim ResTab(1 To d1.Count, 1 To 1)
    i = 0
    For Each k In d1
        i = i + 1
        k1 = Replace(k, "||", "|")
        k1 = Mid(k1, 2, Len(k1) - 2)
        ResTab(i, 1) = k1
    Next k
                
    With Sheets("Sheet2")
        .Range("A1").Resize(d1.Count).Value = ResTab
        .Range("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
         Other:=True, OtherChar:="|"
    End With
    
End Sub

Sub BuildCombo(maxlen, srcdata, curlen, curpos, op1, op2)
Dim i As Long

    If maxlen = curlen Then
        d1.Add op1, 1
        Exit Sub
    End If
    
    For i = curpos + 1 To UBound(srcdata)
        If InStr(op1, "|" & srcdata(i, 1) & "|") = 0 And _
           InStr(op2, "|" & srcdata(i, 2) & "|") = 0 Then
           Call BuildCombo(maxlen, srcdata, curlen + 1, i, op1 & "|" & srcdata(i, 1) & "|", op2 & "|" & srcdata(i, 2) & "|")
        End If
    Next i

End Sub

Go back to the Excel sheet, press Alt-F8, choose StartProc and click Run. This only prints combinations of 2-4, which add up to 202,592 combinations. It can't do the full 1,520,480 since that's more rows than an Excel sheet has. Let me know if you have someplace in mind where you want to put them.

 

[sandy666]

could it be generated into csv with ALL combinations?

 

[Eric W]

It certainly could, that was one of my ideas, but the OP said he wanted it in a spreadsheet. Or I could add another sheet, or put some of the results in columns G:K, or some other options.

 

[sandy666]

sure but one csv can be imported into Power Query Editor easy without any combination of a few ranges then I can manage myself as I want

 

[SuperNerd]

DUDE!!!! THIS IS AWESOME!!! It worked and generated all combos within SECONDS! Yes, how can I get ALL of them, 2-5? I'm good with a second sheet, CSV, etc. CSV sounds good as I'm sure I need to go to Power Query after this.... THANK YOU THANK YOU THANK YOU!!!!

 

[Eric W]

This version will write a csv file with all the combinations:

Option Explicit
Public d1 As Object

Sub StartProc()
Dim MyData(1 To 52, 1 To 2), ResTab()
Dim c As Variant, i As Long, k As Variant, k1 As Variant
Dim OutFile As String, fn As Long, delim As String

    delim = ","
    OutFile = "C:\Temp\combos.csv"

    For Each c In Sheets("Sheet1").Range("A1:M4")
        i = i + 1
        MyData(i, 1) = c.Value
        MyData(i, 2) = c.Column
    Next c
    
    Set d1 = CreateObject("Scripting.Dictionary")
    
    For i = 2 To 5
        Call BuildCombo(i, MyData, 0, 0, "", "")
    Next i
    
    fn = FreeFile
    Open OutFile For Output As #fn
    
    For Each k In d1
        k1 = Replace(k, "||", "|")
        k1 = Mid(k1, 2, Len(k1) - 2)
        k1 = Replace(k1, "|", delim)
        Print #fn, k1
    Next k
    
    Close #fn
    
End Sub

Sub BuildCombo(maxlen, srcdata, curlen, curpos, op1, op2)
Dim i As Long

    If maxlen = curlen Then
        d1.Add op1, 1
        Exit Sub
    End If
    
    For i = curpos + 1 To UBound(srcdata)
        If InStr(op1, "|" & srcdata(i, 1) & "|") = 0 And _
           InStr(op2, "|" & srcdata(i, 2) & "|") = 0 Then
           Call BuildCombo(maxlen, srcdata, curlen + 1, i, op1 & "|" & srcdata(i, 1) & "|", op2 & "|" & srcdata(i, 2) & "|")
        End If
    Next i

End Sub

Set the file name and the delimiter you want in the lines in red. This macro took about 2 minutes to run. I didn't even try raising the number of items to 6, because I suspect it'll take a looong time.