Excel Workbook | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | A1X | A2X | A3X | ||
2 | A1Y | A2Y | A3Y | ||
3 | A1Z | A2Z | A3Z | ||
4 | B1X | B2X | B3X | ||
5 | B1Y | B2Y | B3Y | ||
6 | B1Z | B2Z | B3Z | ||
7 | C1X | C2X | C3X | ||
8 | C1Y | C2Y | C3Y | ||
9 | C1Z | C2Z | C3Z | ||
10 | D1X | D2X | D3X | ||
11 | D1Y | D2Y | D3Y | ||
12 | D1Z | D2Z | D3Z | ||
... |
Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | A1X | B1X | C1X | D1X | ||
2 | A1Y | B1Y | C1Y | D1Y | ||
3 | A1Z | B1Z | C1Z | D1Z | ||
4 | A2X | B2X | C2X | D2X | ||
5 | A2Y | B2Y | C2Y | D2Y | ||
6 | A2Z | B2Z | C2Z | D2Z | ||
7 | A3X | B3X | C3X | D3X | ||
8 | A3Y | B3Y | C3Y | D3Y | ||
9 | A3Z | B3Z | C3Z | D3Z | ||
... |
Cell Formulas | ||
---|---|---|
Range | Formula | |
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 | |||||||
---|---|---|---|---|---|---|---|
G | H | I | J | K | |||
1 | A | B | C | D | |||
2 | 1 | 2 | 3 | 4 | 5 | ||
3 | V | W | X | Y | Z | ||
... |
Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | A1V | B1V | C1V | D1V | ||
2 | A1W | B1W | C1W | D1W | ||
3 | A1X | B1X | C1X | D1X | ||
4 | A2Y | B2Y | C2Y | D2Y | ||
5 | A2Z | B2Z | C2Z | D2Z | ||
6 | A2V | B2V | C2V | D2V | ||
7 | A3W | B3W | C3W | D3W | ||
8 | A3X | B3X | C3X | D3X | ||
9 | A3Y | B3Y | C3Y | D3Y | ||
10 | A4Z | B4Z | C4Z | D4Z | ||
11 | A4V | B4V | C4V | D4V | ||
12 | A4W | B4W | C4W | D4W | ||
13 | A5X | B5X | C5X | D5X | ||
14 | A5Y | B5Y | C5Y | D5Y | ||
15 | A5Z | B5Z | C5Z | D5Z | ||
... |
Cell Formulas | ||
---|---|---|
Range | Formula | |
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 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
M | N | O | P | Q | R | S | T | U | V | |||
1 | A1V | A1W | A1X | B1V | B1W | B1X | C1V | C1W | C2X | C2Y | ||
2 | a | b | c | d | e | f | ||||||
... |
Excel Workbook | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | A1Va | A1Wa | A1Xa | B1Va | B1Wa | B1Xa | C1Va | C1Wa | C2Xa | C2Ya | ||
2 | A1Vb | A1Wb | A1Xb | B1Vb | B1Wb | B1Xb | C1Vb | C1Wb | C2Xb | C2Yb | ||
3 | A1Vc | A1Wc | A1Xc | B1Vc | B1Wc | B1Xc | C1Vc | C1Wc | C2Xc | C2Yc | ||
4 | A1Vd | A1Wd | A1Xd | B1Vd | B1Wd | B1Xd | C1Vd | C1Wd | C2Xd | C2Yd | ||
5 | A1Ve | A1We | A1Xe | B1Ve | B1We | B1Xe | C1Ve | C1We | C2Xe | C2Ye | ||
6 | A1Vf | A1Wf | A1Xf | B1Vf | B1Wf | B1Xf | C1Vf | C1Wf | C2Xf | C2Yf | ||
... |
Cell Formulas | ||
---|---|---|
Range | Formula | |
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) |
Excel Workbook | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | A1 | B1 | C1 | D1 | A | 1 | |||
2 | A2 | B2 | C2 | D2 | B | 2 | |||
3 | A3 | B3 | C3 | D3 | C | 3 | |||
4 | A4 | B4 | C4 | D4 | D | 4 | |||
5 | |||||||||
6 | Named Ranges To Insert While Cursor Is In Cell A1 | ||||||||
7 | Name | Formula | |||||||
8 | Input | =$F$1:$G$4 | |||||||
9 | nRow | =COUNT(ROW(Input)) | |||||||
10 | Permute | =INDEX(Input,COLUMN(A1),COLUMN($A1))&INDEX(Input,MOD(ROW(A1)-1,nRow)+1,ROW(A$1)+1) | |||||||
... |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | =Permute |
Excel Workbook | |||
---|---|---|---|
Name | Refers 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 |
Excel Workbook | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | A1Y | B1Y | A | 1 | Y | |||
2 | A1Z | B1Z | B | 2 | Z | |||
3 | A2Y | B2Y | ||||||
4 | A2Z | B2Z | ||||||
5 | ||||||||
6 | Named Ranges To Insert While Cursor Is In Cell A1 | |||||||
7 | Name | Formula | ||||||
8 | Input | =$D$1:$F$2 | ||||||
9 | nRow | =COUNT(ROW(Input)) | ||||||
10 | Permute | =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 | ||
---|---|---|
Range | Formula | |
A1 | =Permute |
Excel Workbook | |||
---|---|---|---|
Name | Refers 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 |
Excel Workbook | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | A1Ya | B1Ya | A | 1 | Y | a | |||
2 | A1Za | B1Za | B | 2 | Z | b | |||
3 | A2Ya | B2Ya | |||||||
4 | A2Za | B2Za | |||||||
5 | A1Yb | B1Yb | |||||||
6 | A1Zb | B1Zb | |||||||
7 | A2Yb | B2Yb | |||||||
8 | A2Zb | B2Zb | |||||||
9 | |||||||||
10 | Named Ranges To Insert While Cursor Is In Cell A1 | ||||||||
11 | Name | Formula | |||||||
12 | Input | =$D$1:$G$2 | |||||||
13 | nRow | =COUNT(ROW(Input)) | |||||||
14 | Permute | =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) | |||||||
15 | Permute2 | =INDEX(Input,INT((ROW(A1)-1)/nRow^2)+1-INT((ROW(A1)-1)/(nRow^3))*nRow,4) | |||||||
... |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | =Permute&Permute2 |
Excel Workbook | |||
---|---|---|---|
Name | Refers 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 |
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
Excel Workbook | ||||
---|---|---|---|---|
A | B | |||
1 | A | 1 | ||
2 | B | 2 | ||
3 | C | 3 | ||
Sheet2 |
Excel Workbook | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | A1 | B1 | C1 | ||
2 | A2 | B2 | C2 | ||
3 | A3 | B3 | C3 | ||
Sheet1 |
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
Excel Workbook | ||||
---|---|---|---|---|
A | B | |||
1 | A | 1 | ||
2 | B | 2 | ||
3 | C | 3 | ||
Sheet2 |
Excel Workbook | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | A1 | B1 | C1 | ||
2 | A2 | B2 | C2 | ||
3 | A3 | B3 | C3 | ||
Sheet1 |
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