Grouping rows, permutations

[Newonexcel]

Hi to everyone on this forum a few days ago I was looking
for a code to generate 19683 permutations out of three
letters abc and I found it here on mrexcel board RUGILA
gave it to another member I only changed the letters
anyway, I'm now trying to get another code to group all
permutations such as :

AAAAAAAAB = 36
AAAAAAAAC = 36
7A + 2B = 36
7A + 2C = 36
AAAAAAABC = 72p
AAAAAA + 3B = 84
AAAAAA + 3C = 84 and so on.
This is the code I copied from mrexcel dated Dec 18 2009
Code:
Sub allup()
Dim a, n As Integer, c(), k As Long
Dim u1 As Integer, u2 As Integer, u3 As Integer
Dim u4 As Integer, u5 As Integer, u6 As Integer
Dim u7 As Integer, u8 As Integer, u9 As Integer
a = Array("C", "M", "U")
n = UBound(a) + 1
ReDim c(1 To Rows.Count, 1 To 9)
For u1 = 1 To n
For u2 = 1 To n
For u3 = 1 To n
For u4 = 1 To n
For u5 = 1 To n
For u6 = 1 To n
For u7 = 1 To n
For u8 = 1 To n
For u9 = 1 To n
k = k + 1
c(k, 9) = a(u9 - 1)
c(k, 8) = a(u8 - 1)
c(k, 7) = a(u7 - 1)
c(k, 6) = a(u6 - 1)
c(k, 5) = a(u5 - 1)
c(k, 4) = a(u4 - 1)
c(k, 3) = a(u3 - 1)
c(k, 2) = a(u2 - 1)
c(k, 1) = a(u1 - 1)
Next u9, u8, u7, u6, u5, u4, u3, u2, u1
Cells(1).Resize(k, 9) = c
End Sub

If someone one on this forum could help me with a code or
tell me what I can do to get what I want it wil be truly appreciated
I'd like to thank RUGILA and all the people helping others in this
Forum, Thanks in advance.

 

[krausr79]

First off, I think you mean that
aaaaaaaab = 9
aaaaaaaac = 9
because you could choose the b (or c) to be in any of 9 spots and the rest are a's

aaaaaaabc = 72

would be correct because you could choose b to go in any of 9 spots and then c to go in any of 8 remaining spots with the rest all a's.

6a,3b is 84 because you are picking 3 spots of 9 for b to go in. You can calculate this with combinations and permutations. Since the order that the b's are in aren't important, you would use combinations, which excel can do.

=combin(total,choices)
=combin(9,3) = 84

aaaaa bbbb is
=combin(9,4) = 126

This will give you all possible 2-letter combinations. To find the group size when mixing all 3 letters is trickier, and it's getting late, so good luck! Just remember that if your method is working properly, all groups will sum to 19683.

 

[mirabeau]

Hi Newonexcel,

I think Rugila's gone from this forum for some years, so maybe aiming at him/her is not your best approach.

However there's lot's of other contributors here usually willing to help.

For my part, I'm not that sure on just what you want or mean with your
AAAAAAAAB = 36
AAAAAAAAC = 36
7A + 2B = 36
7A + 2C = 36
...
etc.

But if you want a VBA code that will give the same result as the one you posted, then the following should do so, shorter and faster, with four loops instead of nine.

The output is 19683 rows, but what you want done with these is what I don't follow.

Sub perms_loops()
Dim z, y() As String, u&, v&
Dim a&, b&, c&, d&
v = 9
z = Array("a", "b", "c")
u = UBound(z) + 1
ReDim y(1 To u ^ v, 1 To v)
For a = 1 To v
    For b = 1 To u ^ v Step u ^ a
        For c = b To b + u ^ (a - 1) - 1
            For d = 1 To u
                y(c + u ^ (a - 1) * (d - 1), v - a + 1) = z(d - 1)
Next d, c, b, a
Range("A1").Resize(u ^ v, v) = y
End Sub

 

[Newonexcel]

Thanks Krausr79 you're right, what I typed on those two
are wrong. I have all 19683 permutations what I want to
do now is group 'em say:

All 8a + 1b = 9
7a + 2b = 36
7a + 2c = 36
7a + 1b +1c = 72
6a + 3b = 84
6a + 3c = 84
6a + 2b + 1c = 252
6a + 2c + 1b = 252 and so on
I believe I got it right this time, thanks if somebody could
tell me how to do this thanks in advance.