# Permutation in Excel

#### ygilbert

##### New Member
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?

#### Special-K99

##### Well-known Member
Here's one way...

4x3x3 = 36 possibilities

in A1 put 1
right Ctrl-drag the right hand bottom corner of the cell down 36 rows so it generates the numbers 1 to 36

in B1
=MID("ABCD",INT((A1-1)/9)+1,1)&MID("123",INT((A1-(INT((A1-1)/9))*9-1)/3)+1,1)&MID("XYZ",A1-((INT((A1-1)/9))*9+(INT((A1-(INT((A1-1)/9))*9-1)/3)+1-1)*3),1)

and copy down the column

#### JackBean

##### Active Member
Another option:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Cell A1 and copy over and down.

Cells A1:C12:<o></o>
=CHAR(65+INT((ROW(A1)-1)/3))&CHAR(COLUMN(A1)+48)&CHAR(MOD((ROW(A1)-1),3)+88)
Excel Workbook
ABC
1A1XA2XA3X
2A1YA2YA3Y
3A1ZA2ZA3Z
4B1XB2XB3X
5B1YB2YB3Y
6B1ZB2ZB3Z
7C1XC2XC3X
8C1YC2YC3Y
9C1ZC2ZC3Z
10D1XD2XD3X
11D1YD2YD3Y
12D1ZD2ZD3Z
 ...

or:
Cells A1:D9:<o></o>
=CHAR(COLUMN(A1)+64)&CHAR(49+INT((ROW(A1)-1)/3))&CHAR(MOD((ROW(A1)-1),3)+88)
Excel Workbook
ABCD
1A1XB1XC1XD1X
2A1YB1YC1YD1Y
3A1ZB1ZC1ZD1Z
4A2XB2XC2XD2X
5A2YB2YC2YD2Y
6A2ZB2ZC2ZD2Z
7A3XB3XC3XD3X
8A3YB3YC3YD3Y
9A3ZB3ZC3ZD3Z
 ...

Last edited:

#### ygilbert

##### New Member
Thansk alot.

(A,B,C,D) and (1,2,3,4,5) and (V,W,X,Y,Z) and (a,b,c,d,e,f)?

Thanks a million.

YG

#### JackBean

##### Active Member
This formula seems easier to adjust.
You can use a named range, or adjust the range in 3 places in the formula.
Also adjust the number of rows and the number of columns.

=INDEX(\$G\$1:\$K\$3,ROW(A\$1),COLUMN(A9))&INDEX(\$G\$1:\$K\$3,COLUMN(\$B2),INT(ROW(A2)-2)/3+1)&INDEX(\$G\$1:\$K\$3,COLUMN(\$C1),MOD(ROW(A1)-1,5)+1)
Cell Formulas
CellFormula
A1=INDEX(\$G\$1:\$K\$3,ROW(A\$1),COLUMN(A9))&INDEX(\$G\$1:\$K\$3,COLUMN(\$B2),INT(ROW(A2)-2)/3+1)&INDEX(\$G\$1:\$K\$3,COLUMN(\$C1),MOD(ROW(A1)-1,5)+1)
Excel Workbook
GHIJK
1ABCD
212345
3VWXYZ
 ...
Excel Workbook
ABCD
1A1VB1VC1VD1V
2A1WB1WC1WD1W
3A1XB1XC1XD1X
4A2YB2YC2YD2Y
5A2ZB2ZC2ZD2Z
6A2VB2VC2VD2V
7A3WB3WC3WD3W
8A3XB3XC3XD3X
9A3YB3YC3YD3Y
10A4ZB4ZC4ZD4Z
11A4VB4VC4VD4V
12A4WB4WC4WD4W
13A5XB5XC5XD5X
14A5YB5YC5YD5Y
15A5ZB5ZC5ZD5Z
 ...

Then more can be added on to the result for more combinations.

For example, take some items from the result above and add the a,b,c,d,e.
Cell Formulas
CellFormula
A1=INDEX(\$M\$1:\$V\$3,ROW(A\$1),COLUMN(A9))&INDEX(\$M\$1:\$V\$3,COLUMN(\$B2),INT(ROW(A2)-2)+1)
Excel Workbook
MNOPQRSTUV
1A1VA1WA1XB1VB1WB1XC1VC1WC2XC2Y
2abcdef
 ...
Excel Workbook
ABCDEFGHIJ
1A1VaA1WaA1XaB1VaB1WaB1XaC1VaC1WaC2XaC2Ya
2A1VbA1WbA1XbB1VbB1WbB1XbC1VbC1WbC2XbC2Yb
3A1VcA1WcA1XcB1VcB1WcB1XcC1VcC1WcC2XcC2Yc
4A1VdA1WdA1XdB1VdB1WdB1XdC1VdC1WdC2XdC2Yd
5A1VeA1WeA1XeB1VeB1WeB1XeC1VeC1WeC2XeC2Ye
6A1VfA1WfA1XfB1VfB1WfB1XfC1VfC1WfC2XfC2Yf
 ...

I have not looked at combining 4 or more in one step.
This looks like a good programming problem.

#### JackBean

##### Active Member
Correction of formula in previous post:

The RED 3 in the formula has been changed to 5 and this refers also to the number of columns, not rows.

Also the formula is filled into Cells A1 to D25.
This gives 4x5x5 combinations.
Cell Formulas
CellFormula
A1=INDEX(\$G\$1:\$K\$3,ROW(A\$1),COLUMN(A1))&INDEX(\$G\$1:\$K\$3,COLUMN(\$B1),INT(ROW(A1)-1)/5+1)&INDEX(\$G\$1:\$K\$3,COLUMN(\$C1),MOD(ROW(A1)-1,5)+1)

#### JackBean

##### Active Member
This can be made easier to use by using Named Formulas.
Also the Input can be put in Columns instead of Rows to allow longer lists.
The three examples below are separate workbooks to avoid conflict with the identical Named Formulas.

Input is the only Named Formula that needs to be changed when the input range changes.

For combinations of pairs:
Excel Workbook
ABCDEFG
1A1B1C1D1A1
2A2B2C2D2B2
3A3B3C3D3C3
4A4B4C4D4D4
5
6Named Ranges To Insert While Cursor Is In Cell A1
7NameFormula
8Input=\$F\$1:\$G\$4
9nRow=COUNT(ROW(Input))
10Permute=INDEX(Input,COLUMN(A1),COLUMN(\$A1))&INDEX(Input,MOD(ROW(A1)-1,nRow)+1,ROW(A\$1)+1)
 ...
Cell Formulas
CellFormula
A1=Permute
Excel Workbook
NameRefers To
Permute=INDEX(Input,COLUMN(Sheet1!A1),COLUMN(Sheet1!\$A1))&INDEX(Input,MOD(ROW(Sheet1!A1)-1,nRow)+1,ROW(Sheet1!A\$1)+1)
 Workbook Defined Names

Combinations of threes:
Excel Workbook
ABCDEF
1A1YB1YA1Y
2A1ZB1ZB2Z
3A2YB2Y
4A2ZB2Z
5
6Named Ranges To Insert While Cursor Is In Cell A1
7NameFormula
8Input=\$D\$1:\$F\$2
9nRow=COUNT(ROW(Input))
10Permute=INDEX(Input,COLUMN(A1),COLUMN(\$A1))&INDEX(Input,INT(ROW(A1)-1)/nRow+1,COLUMN(\$A1)+1)&INDEX(Input,MOD(ROW(A1)-1,nRow)+1,ROW(A\$1)+2)
 ...
Cell Formulas
CellFormula
A1=Permute
Excel Workbook
NameRefers To
Permute=INDEX(Input,COLUMN(Sheet1!A1),COLUMN(Sheet1!\$A1))&INDEX(Input,INT(ROW(Sheet1!A1)-1)/nRow+1,COLUMN(Sheet1!\$A1)+1)&INDEX(Input,MOD(ROW(Sheet1!A1)-1,nRow)+1,ROW(Sheet1!A\$1)+2)
 Workbook Defined Names

For the next example the "Permute" Named Formula is split into "Permute" and "Permute2" because of its length.
Combinations of fours:
Excel Workbook
ABCDEFG
1A1YaB1YaA1Ya
2A1ZaB1ZaB2Zb
3A2YaB2Ya
4A2ZaB2Za
5A1YbB1Yb
6A1ZbB1Zb
7A2YbB2Yb
8A2ZbB2Zb
9
10Named Ranges To Insert While Cursor Is In Cell A1
11NameFormula
12Input=\$D\$1:\$G\$2
13nRow=COUNT(ROW(Input))
14Permute=INDEX(Input,COLUMN(A1),COLUMN(\$A1))&INDEX(Input,INT((ROW(A1)-1)/nRow)+1-INT((ROW(A1)-1)/(nRow^2))*nRow,2)&INDEX(Input,MOD(ROW(A1)-1,nRow)+1,ROW(A\$1)+2)
15Permute2=INDEX(Input,INT((ROW(A1)-1)/nRow^2)+1-INT((ROW(A1)-1)/(nRow^3))*nRow,4)
 ...
Cell Formulas
CellFormula
A1=Permute&Permute2
Excel Workbook
NameRefers To
Permute=INDEX(Input,COLUMN(Sheet1!A1),COLUMN(Sheet1!\$A1))&INDEX(Input,INT((ROW(Sheet1!A1)-1)/nRow)+1-INT((ROW(Sheet1!A1)-1)/(nRow^2))*nRow,2)&INDEX(Input,MOD(ROW(Sheet1!A1)-1,nRow)+1,ROW(Sheet1!A\$1)+2)
Permute2=INDEX(Input,INT((ROW(Sheet1!A1)-1)/nRow^2)+1-INT((ROW(Sheet1!A1)-1)/(nRow^3))*nRow,4)
 Workbook Defined Names

#### JackBean

##### Active Member
This can also be done as below for pairs only with a Sub (more convenient) or a UDF.

First the Sub:

In the Sheet2 Code Module place this Code:

Code:
``````Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim LastRow As Long, X As Long, Y As Long
Dim rInput As Range, vInput As Variant, vOutput As Variant
With Sheet2.Range("A1")
LastRow = .CurrentRegion.Rows.Count
Set rInput = .CurrentRegion
End With
ReDim vInput(1 To LastRow, 1 To 2)
vInput = rInput
ReDim vOutput(1 To LastRow, 1 To LastRow)
For Y = 1 To LastRow
For X = 1 To LastRow
vOutput(Y, X) = vInput(X, 1) & vInput((Y - 1 Mod LastRow) + 1, 2)
Next X
Next Y
With Sheet1.Range("A1")
.CurrentRegion.Clear
.Resize(LastRow, LastRow) = vOutput
End With
Application.EnableEvents = True
End Sub``````
The pairs of items go on Sheet2:
Excel Workbook
AB
1A1
2B2
3C3
 Sheet2

Any Cell change on Sheet2 causes the sub to run with the result on Sheet1:
Excel Workbook
ABC
1A1B1C1
2A2B2C2
3A3B3C3
 Sheet1

Then the UDF:

In a standard Code Module place this Code:

Code:
``````Option Explicit
Function Perm(rInput As Range) As Variant
Dim LastRow As Long, X As Long, Y As Long
Dim vInput As Variant, vOutput As Variant
vInput = rInput
LastRow = UBound(vInput, 1)
ReDim vOutput(1 To LastRow, 1 To LastRow)
For Y = 1 To LastRow
For X = 1 To LastRow
vOutput(Y, X) = vInput(X, 1) & vInput((Y - 1 Mod LastRow) + 1, 2)
Next X
Next Y
Perm = vOutput
End Function``````
As before the pairs of items go on Sheet2:
Excel Workbook
AB
1A1
2B2
3C3
 Sheet2

The UDF is array entered on Sheet1.
Start in Cell A1 and select Cell A1 to C3.
Enter =Perm(Sheet2!A1:B3) with Control Shift Enter.
The results are the same as for the sub:
Excel Workbook
ABC
1A1B1C1
2A2B2C2
3A3B3C3
 Sheet1

Neither "PERMUT" or "PERMUTE" could be used for the name of the UDF.
There is an Excel Function "PERMUT".
Array Formulas
CellArray Formula
A1:C3=Perm(Sheet2!A1:B3)
Press CTRL+SHIFT+ENTER to enter array formulas.
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

#### pgc01

##### MrExcel MVP
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:

Nice one.