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