How to generate all possible combinations of two lists (without MACRO)?

[erangai]

Hi everyone,

I need to make a list of all possible combinations of two lists as shown in the following example.
i.e. Basically when I update Column A & B, the combinations should get appear in the “Output” column.
Also need to do this without running a MACRO.

Can someone kindly help me?

Example

Column A	Column B		Output (Combinations)
Mar	AA		MarAA
Apr	BB		MarBB
May	CC		MarCC
	DD		MarDD
			AprAA
			AprBB
			AprCC
			AprDD
			MayAA
			MayBB
			MayCC
			MayDD

 

[Eric W]

Check out post #2 from this thread:

https://www.mrexcel.com/forum/excel-questions/1020834-modify-code-run-all-possible-combinations.html

 

[tsatte]

Awesome, that is almost exactly what I am looking for! I like that I can use any number of columns in sheet 1 and I can stop the output at row 1,000,000, but is it possible to have it to put the output in separate columns and then skip 4 columns and start with the next set of permutations?

To keep it simple, I only used 2 columns of input in my example:

Here is the input for sheet 1:

INPUT
	A	B
1	aa11	11
2	2	c
3	b	33

<tbody>
</tbody>

Then I get this:

CURRENT
	A
1	aa11
2	aac
3	aa33
4	211
5	2c
6	233
7	b11
8	bc
9	b33

<tbody>
</tbody>

How can I change the code to get this instead in sheet 2 where the permutations are broken out into individual cells and 4 columns are left in between each 1,000,000 rows of permutations like so:

DESIRED
	A	B	C	D	E	F	G	H	I	J
1	aa	11					After Row 1,000,000 the Next permutations start here
2	aa	c
3	aa	33
4	2	11
5	2	c
6	2	33
7	b	11
8	b	c
9	B	33

<tbody>
</tbody>

Here is your code below that was used (also the words input, current, and desired are not needed, they were only used to help with this example):

Sub Permute()
Dim ix(100, 1) As Long, rc As Long, m As Long, br As Long, md As Variant, i As Long, r As Long
Dim str1 As String, r1 As Long, c1 As Long

rc = Cells(1, Columns.Count).End(xlToLeft).Column
m = 0
For i = 1 To rc
br = Cells(Rows.Count, i).End(xlUp).Row
If br > m Then m = br
ix(i, 0) = br
ix(i, 1) = 1
Next i
md = Range(Cells(1, 1), Cells(m, rc)).Value

r = 0
Incr:
str1 = ""
For i = 1 To rc
str1 = str1 & md(ix(i, 1), i)
Next i
r = r + 1
r1 = ((r - 1) Mod 1000000) + 1
c1 = Int((r - 1) / 1000000) + 1
Sheets("Sheet2").Cells(r1, c1) = str1

For i = rc To 1 Step -1
ix(i, 1) = ix(i, 1) + 1
If ix(i, 1) <= ix(i, 0) Then Exit For
ix(i, 1) = 1
Next i
If i > 0 Then GoTo Incr:

End Sub

Check out post #2 from this thread:

https://www.mrexcel.com/forum/excel-questions/1020834-modify-code-run-all-possible-combinations.html

 

[Eric W]

Try:

Sub Permute()
Dim ix(100, 1) As Long, rc As Long, m As Long, md As Variant, i As Long, r As Long, c As Long
Dim MyOut() As Variant, mor As Long, cbr As Long

' Set your max output rows here:
    mor = 1000000
' Set columns between results here:
    cbr = 4
    
    rc = Cells(1, Columns.Count).End(xlToLeft).Column
    m = 0
    For i = 1 To rc
        ix(i, 0) = Cells(Rows.Count, i).End(xlUp).Row
        ix(i, 1) = 1
        m = IIf(ix(i, 0) > m, ix(i, 0), m)
    Next i
    md = Range(Cells(1, 1), Cells(m, rc)).Value
    ReDim MyOut(1 To mor, 1 To rc)

    r = 0
    c = 1
Incr:
    r = r + 1
    If r > mor Then
        Sheets("Sheet2").Cells(1, c).Resize(mor, rc).Value = MyOut
        r = 1
        c = c + rc + cbr
        ReDim MyOut(1 To mor, 1 To rc)
    End If
    
    For i = 1 To rc
        MyOut(r, i) = md(ix(i, 1), i)
    Next i
    
    For i = rc To 1 Step -1
        ix(i, 1) = ix(i, 1) + 1
        If ix(i, 1) <= ix(i, 0) Then GoTo Incr:
        ix(i, 1) = 1
    Next i
    
    Sheets("Sheet2").Cells(1, c).Resize(mor, rc).Value = MyOut
    
End Sub

Change the values in red as desired. I tweaked it a bit to make it a bit faster. But I reiterate, I don't see the purpose of printing out millions of combinations. Better to analyze them as created if need be.

 

[tsatte]

Eric, you are the genius that I have read you are!You are correct, it is too much data.

Is there a way where I enter the input below, and it does not print duplicates?

1	1	1	1
2	2	2	2
3	3	3	3
4	4	4	4
5	5	5	5
6	6	6	6

<tbody>
</tbody>

These are examples of permutations that would not show up in sheet 2:

1	1	1	1
1	2	2	1
2	2	3	3
4	1	1	4
5	3	5	1
2	6	4	6

<tbody>
</tbody>

These are examples of permutations that would show up in sheet 2

1	2	3	4
2	4	5	6
1	2	5	6
4	1	2	5
5	3	5	1
2	5	4	6

<tbody>
</tbody>

Also is there a way to have only one of the following show up in sheet 2 since they are all virtually the same 4 numbers:

1	2	3	4
1	2	4	3
1	3	2	4
1	3	4	2
1	4	3	2
1	4	2	3
2	1	3	4
2	1	4	3
2	3	1	2
2	3	1	4
2	4	1	3
2	4	3	1

<tbody>
</tbody>

 

[Eric W]

I think I'm going to have to tap out at this point. I actually modified the code to handle the conditions you said, but it was painfully slow. In order to get it to run in a reasonable amount of time, I'd have to write sort routines and other types of optimizations. And I think that would be a lot of work for very little return. Maybe if you explained the ultimate goal of your analysis I could suggest an alternate approach. However, keep in mind that if you're using this to look at lottery numbers or some such thing, this kind of analysis will most certainly not help.

 

[tsatte]

Hi Eric,

I work for a security firm and need to generate multiple rounds for guards.

For instance

I can use their names:

j.green	1	1	1
b.opensheim	2	2	2
a.gardener	3	3	3
d.bryant	4	4	4
m.whitaker	5	5	5
o.stuart	6	6	6

<tbody>
</tbody>

But it’s easier to use their Id numbers in the first block in place of their names.

5910685	1	1	1
5901763	2	2	2
5903425	3	3	3
5907277	4	4	4
5901322	5	5	5
5902455	6	6	6

<tbody>
</tbody>

In my examples I was trying to make it simple with using small number

1	1	1	1
2	2	2	2
3	3	3	3
4	4	4	4
5	5	5	5
6	6	6	6

<tbody>
</tbody>

or letters as they are easier to me to explain.

a	a	a	a
b	b	b	b
c	c	c	c
d	d	d	d
e	e	e	e
f	f	f	f

<tbody>
</tbody>

What my input will look like is below:

5910685	1	1	1
5901763	2	2	2
5903425	3	3	3
5907277	4	4	4

<tbody>
</tbody>

My largest site has 55 guards between 3 shifts where they have to hit the access points in column B through L which is 11 points. The shifts are broken down to at most 18 guards on 1 shift. This output will help me to randomize their rounds. Having the duplicates removed would be most helpful. Second only the order of 1, 2, 4, versus having 2,1,4, and 2,4,1, and 1,4, 2 and others is helpful because the location of the guard station dictates where they start, I can live with this one, but if there was and easy way to remove it, it would help too.

I really appreciate your help.

I think I'm going to have to tap out at this point. I actually modified the code to handle the conditions you said, but it was painfully slow. In order to get it to run in a reasonable amount of time, I'd have to write sort routines and other types of optimizations. And I think that would be a lot of work for very little return. Maybe if you explained the ultimate goal of your analysis I could suggest an alternate approach. However, keep in mind that if you're using this to look at lottery numbers or some such thing, this kind of analysis will most certainly not help.

 

[tsatte]

Hi Eric,

I work for a security firm and need to generate multiple rounds for guards.

For instance

I can use their names:

j.green	1	1	1
b.opensheim	2	2	2
a.gardener	3	3	3
d.bryant	4	4	4
m.whitaker	5	5	5
o.stuart	6	6	6

<tbody>
</tbody>

But it’s easier to use their Id numbers in the first block in place of their names.

5910685	1	1	1
5901763	2	2	2
5903425	3	3	3
5907277	4	4	4
5901322	5	5	5
5902455	6	6	6

<tbody>
</tbody>

In my examples I was trying to make it simple with using small number

1	1	1	1
2	2	2	2
3	3	3	3
4	4	4	4
5	5	5	5
6	6	6	6

<tbody>
</tbody>

or letters as they are easier to me to explain.

a	a	a	a
b	b	b	b
c	c	c	c
d	d	d	d
e	e	e	e
f	f	f	f

<tbody>
</tbody>

What my input will look like is below:

5910685	1	1	1
5901763	2	2	2
5903425	3	3	3
5907277	4	4	4

<tbody>
</tbody>

My largest site has 55 guards between 3 shifts where they have to hit the access points in column B through L which is 11 points. The shifts are broken down to at most 18 guards on 1 shift. This output will help me to randomize their rounds. Having the duplicates removed would be most helpful. Second only the order of 1, 2, 4, versus having 2,1,4, and 2,4,1, and 1,4, 2 and others is helpful because the location of the guard station dictates where they start, I can live with this one, but if there was and easy way to remove it, it would help too.

I really appreciate your help.

 

[Eric W]

Sorry I haven't gotten back sooner. I may not be able to work on this for a while, so I'd recommend opening a new thread. Get some additional eyes on it. Adding to an old thread often isn't a great idea, since people don't always see new posts on old threads.

If you do, try to be a bit more specific in your requirements. Say that column 1 has ID numbers, and the other columns are checkpoints. And you're looking for a way to randomize the routes. Give a few examples of valid results. I'd also recommend just asking for a macro that creates a list of say 10 routes, which you can rerun whenever necessary. There's no need to print them all. You could save the used routes so that they can be excluded in the future, but with millions of routes to choose from, the odds of that happening are vanishingly small.

Good luck!

 

[tsatte]

Eric,

Thank you so much you have been extremely helpful thus far! It is a pain to come up with these unique routes for 17 different contracts. I will take your advice and repost!

 

[tsatte]

Tusharm,

Please help, I need to generate rounds for security guards at multiple stations and I am looking to see if someone can help me using the code below to create a macro that would print only the unique permutations/combinations in sheet 2:

For instance in sheet 1 my input would be the guards ID number in A1 5910685 and B1 would equal 1 and C2 would equal 1 just like below.

5910685	1	1	1
5901763	2	2	2
5903425	3	3	3
5907277	4	4	4
5901322	5	5	5
5902455	6	6	6

<tbody>
</tbody>

You can use 1 instead of the id number 5910685 and 2 instead of 5901763 and so on so the input can be:

1	1	1	1
2	2	2	2
3	3	3	3
4	4	4	4
5	5	5	5
6	6	6	6

<tbody>
</tbody>

Here are some examples of Bad output that should not showup in Sheet 2 because there are duplicate numbers in each row:

1	1	1	1
1	2	2	1
2	2	3	3
4	1	1	4
5	3	5	1
2	6	4	6

<tbody>
</tbody>

Good output in Sheet 2 would look like this:

1	2	3	4
2	4	5	6
1	2	5	6
4	1	2	5
5	3	5	1
2	5	4	6

<tbody>
</tbody>

The code below will allow me to specify what row to end on (1,000,000) and how many columns separate the data (4) in sheet 2.


Sub Permute()
Dim ix(100, 1) As Long, rc As Long, m As Long, md As Variant, i As Long, r As Long, c As Long
Dim MyOut() As Variant, mor As Long, cbr As Long

' Set your max output rows here:
mor = 1000000
' Set columns between results here:
cbr = 4

rc = Cells(1, Columns.Count).End(xlToLeft).Column
m = 0
For i = 1 To rc
ix(i, 0) = Cells(Rows.Count, i).End(xlUp).Row
ix(i, 1) = 1
m = IIf(ix(i, 0) > m, ix(i, 0), m)
Next i
md = Range(Cells(1, 1), Cells(m, rc)).Value
ReDim MyOut(1 To mor, 1 To rc)

r = 0
c = 1
Incr:
r = r + 1
If r > mor Then
Sheets("Sheet2").Cells(1, c).Resize(mor, rc).Value = MyOut
r = 1
c = c + rc + cbr
ReDim MyOut(1 To mor, 1 To rc)
End If

For i = 1 To rc
MyOut(r, i) = md(ix(i, 1), i)
Next i

For i = rc To 1 Step -1
ix(i, 1) = ix(i, 1) + 1
If ix(i, 1) <= ix(i, 0) Then GoTo Incr:
ix(i, 1) = 1
Next i

Sheets("Sheet2").Cells(1, c).Resize(mor, rc).Value = MyOut

End Sub


The other thing on my wish list is to have only one of the following show up in my list because they are virtually all the same number (I didn’t include all of the combinations but just a few for 1,2,3,4):

1	2	3	4
1	2	4	3
1	3	2	4
1	3	4	2
1	4	3	2
1	4	2	3
2	1	3	4
2	1	4	3
2	3	1	2
2	3	1	4
2	4	1	3
2	4	3	1

<tbody>
</tbody>

I am ok if the last item can't be done, but it sure would be helpful!