PDA

View Full Version : Permutation List Code



prototype
07-01-2014, 07:19 PM
Hi,

I would like to know how to populate 27 rows with the 27 permutations of a,b,c (in different columns of each row). So it would look something like
a,a,a
b,a,a
b,b,a
b,b,b
a,b,b
c,a,a
... to 27th row

This would probably require a triple nested loop. This code in javascript does the trick, but I am unable to translate something like it into vba. Here's the javascript code if it helps:

var set = ['a', 'b', 'c'];
var result = [];

var elt = ";
for (var i = 0 < set.length; i++) {
for (var j = 0; j < set.length; j++) {
for (var k = 0; k< set.length; k++) {
elt = set[i] + set[j] + set[k];
result.push(elt);
}
}
}

Thanks for your help!

Paul_Hossler
07-01-2014, 08:17 PM
Not the most efficient, but close to your example



Option Explicit
Option Base 1
Sub ABC()
Dim i1 As Long, i2 As Long, i3 As Long, iOut As Long
Dim aABC As Variant

aABC = Array("a", "b", "c")

iOut = 1

Application.ScreenUpdating = False

For i1 = LBound(aABC) To UBound(aABC)
For i2 = LBound(aABC) To UBound(aABC)
For i3 = LBound(aABC) To UBound(aABC)
ActiveSheet.Cells(iOut, 1).Value = aABC(i1)
ActiveSheet.Cells(iOut, 2).Value = aABC(i2)
ActiveSheet.Cells(iOut, 3).Value = aABC(i3)
iOut = iOut + 1
Next i3
Next i2
Next i1

Application.ScreenUpdating = True
End Sub

lecxe
07-02-2014, 04:55 AM
Hi

Just for fun, a one-liner (specific to this problem):



Sub Permut()

Range("C2").Resize(27, 3).Value = Application.Index(Array("a", "b", "c"), Evaluate("if(row(1:27),mod(int((row(1:27)-1)/{9,3,1}),3)+1)"))
End Sub

snb
07-02-2014, 07:02 AM
another one:

Sub M_snb()
Cells(1, 5).Resize(27) = [index(char(97+int((row(1:27)-1)/9)) & char(97+int(mod((row(1:27)-1),9)/3))& char(97+mod(row(1:27)-1,3)),)]
End Sub

or even:


Sub Permuta()
Range("C2").Resize(27, 3).Value = [Index(char(96+mod(int((row(1:27)-1)/{9,3,1}),3)+1),)]
End Sub

prototype
07-02-2014, 06:14 PM
Thanks everyone, I really appreciate it!