Permutation in Excel

ygilbert

New Member
Joined
Jul 12, 2008
Messages
44
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?
 
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 :rolleyes:

Much appreciated
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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?
 
Upvote 0
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:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,022
Latest member
RobertV1609

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