Solved How do I create a macro that makes permutation tables?

April 2, 2017 at 05:00:20
Specs: Macintosh
Hello, I am someone with limited programming knowledge, but I urgently need to generate a table of permutations. Specifically, I need to make a table that has (in this case) 5 columns, each representing a "trait" that can each be at a value of "high", "medium" or "low". I need the table to consist of each possible combination of traits (for example, high high high high high).

To do this, it would be great to use an excel macro that could be easily modifiable to edit the number of states each trait exists in, as well as change the number of traits.

I have tried doing this by using the following code (which I got from another help forum), but I get a compile error.

< pre > Sub PermutationsTable()

'First, define your array of values
Dim MyArray(1)

MyArray(0) = "L"
MyArray(1) = "M"
MyArray(2) = "H"

Col = 1

'Now, "print" the combinations by looping through your array

For g = 0 To 1
For h = 0 To 1
For i = 0 To 1
For j = 0 To 1
For k = 0 To 1

****Cells(1, Col) = MyArray(g)
****Cells(2, Col) = MyArray(h)
****Cells(3, Col) = MyArray(i)
****Cells(4, Col) = MyArray(j)
****Cells(5, Col) = MyArray(k)
****Col = Col + 1

Next k
Next j
Next i
Next h
Next g

End Sub </ pre >

Thank you!


See More: How do I create a macro that makes permutation tables?

Report •

#1
April 2, 2017 at 06:16:22
✔ Best Answer
Well, first let's fix the macro you posted, then we'll modify it to give you what you want.

1 - Posting the code: There should not be spaces within the pre tag operator. If you click the pre tag symbol above the Post Reply box, you'll get the correct format.

2 - Obviously, the asterisks need to go away. e.g.

****Cells(1, Col) = MyArray(g)

becomes

Cells(1, Col) = MyArray(g)

3 - Next, We need to fix this:

Dim MyArray(1)

MyArray(0) = "L"
MyArray(1) = "M"
MyArray(2) = "H"

When you Dim an Array as (1), you only get 2 elements - 0 and 1. As soon as the code tries to set the 3rd element MyArray(2) you are going to get a subscript out of range error.

Dim MyArray(1)

becomes

Dim MyArray(2)


4 - Since you have 3 elements in the array, you need to expand the loop. e.g.

For g = 0 To 1

becomes

For g = 0 To 2

The same holds for h:k

OK, once those changes are made, that is going to give you all the permutations of L, M and H spread out across A1:II5, with each permutation in a Column. Of course, that is not what you want. I believe that you need the permutation output in rows.

In any case, here is the "fixed" original code just so that you can see the changes:

Sub PermutationsTable()

'First, define your array of values
Dim MyArray(2)

MyArray(0) = "L"
MyArray(1) = "M"
MyArray(2) = "H"

Col = 1

'Now, "print" the combinations by looping through your array

 For g = 0 To 2
  For h = 0 To 2
   For i = 0 To 2
    For j = 0 To 2
     For k = 0 To 2

      Cells(1, Col) = MyArray(g)
      Cells(2, Col) = MyArray(h)
      Cells(3, Col) = MyArray(i)
      Cells(4, Col) = MyArray(j)
      Cells(5, Col) = MyArray(k)
       Col = Col + 1

     Next k
    Next j
   Next i
  Next h
 Next g

End Sub

Now, since you said you have 5 columns, we need to transpose the table so that the permutations of L, M and H end up in rows under the trait. We do that by incrementing the Row argument, not the Column argument, in the loop.

If you start with this table, the following code will list each permutation horizontally in Row 2:244

        A           B           C           D           E   
1     Trait 1     Trait 2     Trait 3     Trait 4     Trait 5    

Sub PermutationsTable()

'First, define your array of values
Dim MyArray(2)

MyArray(0) = "L"
MyArray(1) = "M"
MyArray(2) = "H"

rw = 2

'Now, "print" the combinations by looping through your array

 For g = 0 To 2
  For h = 0 To 2
   For i = 0 To 2
    For j = 0 To 2
     For k = 0 To 2

      Cells(rw, 1) = MyArray(g)
      Cells(rw, 2) = MyArray(h)
      Cells(rw, 3) = MyArray(i)
      Cells(rw, 4) = MyArray(j)
      Cells(rw, 5) = MyArray(k)
       rw = rw + 1

     Next k
    Next j
   Next i
  Next h
 Next g

End Sub

For now, that should get you what you need. In a separate response I'll offer a solution for varying the number of Traits and States, as that adds another layer of complexity to the matter. I don't have time to work on that right now. Obviously, manually changing the loops in the code above will accomplish that goal, but I'm sure that there is a better way.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
April 2, 2017 at 06:23:49
EDIT: I wasn't expecting an answer so quickly so I worked on it on my own, but thank you for your speedy and great answer! Thats exactly what I did!

It would be great to have the function setup to be able to automatically update the number of traits and states required. If you could get back to me when you have some time, that would be amazing! Thank you!

message edited by StrugglingQUStudent


Report •
Related Solutions


Ask Question