Permutation in Excel

[ygilbert]

I have the following:

(A,B,C,D) and (1,2,3) and (X,Y,Z)

So I would like to get the following combinations

A1X,A1Y,A1Z, A2X,A2Y,A2Z, ..., D3X,D3Y,D3Z

How is this done in excel?

 

[MaxTrax]

Ok, thanks again Cindy I will work it out from here. If you think of anymore which may help with this recalculation bit as it is very important please post.

Anybody like to try for a formula for this? It is way beyond me

Much appreciated

 

[Jkidd]

Is there a way to get a column to cycle through all combinations for one column, but exclude that for others and only take 1 variable. For ex. let's take just column a and b. I woulid want to know this. A+all the combinations withing B. so, the combinations in this instance are

A1&B1
A1&B2
A1&B3
A1&B1&B2
A1&B1&B3
A1&B2&B3

A2&B1
A2&B2
A2&B3
A2&B1&B2
A2&B1&B3
A2&B2&B3

No repeats and I wouldn't need the A1+A2 Combination as those are mutually exclusive, while the other columns can be inclusive of all elements in said column.

So some columns I ONLY want one of the variables, and others, I'd want to cycle through all combinations of variables within a column.

DOes this make sense?

 

[Sethnick]

Hi

Another option, using vba.

This should work with any number of sets, each with any number of elements.

Write the sets in contiguous columns, starting in column A. Write each set in contiguous rows starting in row 1. Leave the column after the last set empty.

Try:

Sub Perm()
Dim rSets As Range, rOut As Range
Dim vArr As Variant, lrow As Long
 
Set rSets = Range("A1").CurrentRegion
ReDim vArr(1 To rSets.Columns.Count)
Set rOut = Cells(1, rSets.Columns.Count + 2)
Perm1 rSets, vArr, rOut, 1, lrow
End Sub
 
Sub Perm1(rSets As Range, ByVal vArr As Variant, rOut As Range, ByVal lSetN As Long, lrow As Long)
Dim j As Long
 
For j = 1 To rSets.Rows.Count
    If rSets(j, lSetN) = "" Then Exit Sub
    vArr(lSetN) = rSets(j, lSetN)
    If lSetN = rSets.Columns.Count Then
        lrow = lrow + 1
        rOut(lrow).Resize(1, rSets.Columns.Count).Value = vArr
    Else
        Perm1 rSets, vArr, rOut, lSetN + 1, lrow
    End If
Next j
End Sub

Ex:


[TABLE="width: 2"]
<tbody>[TR]
[TH][/TH]
[TH="width: 30, align: center"]A[/TH]
[TH="width: 30, align: center"]B[/TH]
[TH="width: 30, align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[TH="width: 30, align: center"]F[/TH]
[TH="width: 30, align: center"]G[/TH]
[TH="width: 30, align: center"]H[/TH]
[TH="width: 30, align: center"]I[/TH]
[TH="width: 30, align: center"]J[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b1[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b2[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c1[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c2[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]36[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]b3[/TD]
[TD="align: center"]c3[/TD]
[TD="align: center"]d2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="colspan: 11"] [Book1]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

Hello,

I realize this post and thread are quite old, but I am hopeful I may find some additional help on it. I am trying to do something similar to what this script does, but with one slight difference and I have searched quite a bit with no luck. What if column B (or C) happened to have a duplicate of an entry in column A, and I want to generate all permutations without any that have a duplicate from one column to another. For example let's say column A and B both contain "a2"...is there a script to be able to list all permutations without any that would include "a2 a2 c1 d1" and so on? To further clarify, if i have 4 or 5 columns of names and some names appear in multiple columns, and I want the permutations of all names, but don't need the same name to appear twice in a row, can that be done? I also would like to have a header row if possible and be able to do what the above script does in terms of any amount of columns or names. Thank you very much in advance for any help.