All possible combinations with repeats allowed

[Gadgetguy]

Hello Everybody,
I'm looking for a way to have excel display (make a list) of all possible 9 digits/spaces combinations using three letters: CMU, where the letters can be repeated.
I searched this forum and the internet. I've google it different ways and I still haven't figure out the answer. I'm not an expert in excel but I know the basics, and with instructions from someone else I can work my way around it. I don't know how many combinations there will be or if is even possible to do. I'll give an example of what I am looking for. I want excel to list all possible combinations like this:

CCCCCCCCC
MMMMMMMMM
UUUUUUUUU
CMMMMMMMM
UMMMMMMMM
MMUUUUUUU
etc....

All the threads I've found talked about combinations and permutations but none fit or give enough information on how to do something like the example above. Can someone please help me? Any help will be highly appreciated.

 

[Legacy 14611]

Happy with a macro?
Just run it and see if it's like what you want.

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

 

[Gadgetguy]

YES!!! this is exactly what I was looking for. Thank you very much. It would be awesome to find out if there are other ways(methods) to do this very same thing, but, is not necessary. I am super HAPPY!! .
Rugila, Thank you very much for your help. I will try to understand what you did. I don't know all the rules of this forum just yet (I joined a few hours ago); I don't know if I have to start a new thread for my next question, but, if I do I will. How would I calculate the number of possibilities of something like this? I mean I've used "Combination" and "Permutation" fuctions from excel but I didn't get sooo many combinations. Thank you very much for helping me out.

 

[pgc01]

Hi
Welcome to the board

This is an example of a code that will calculate the permutations with repetition of any number of elements with any number of positions.

Notice that the number of permutations with repetition grows very fast and so before executing the code you better calculate if there are enough rows in your worksheet to store them.

The number of permutations with repetion is

PermR(n, p) = n^p

In your example, PermR(3, 9) = 3^9 = 19683

In this example I calculate the permutations as string, you can easily calculate them as arrays if you prefer.

Insert a new module and paste:

Option Explicit
 
Dim arrElements As Variant
Dim arrResult As Variant
Dim p As Long
 
Sub Test()
Dim lRow As Long
 
arrElements = Array("C", "M", "U")
p = 9
ReDim arrResult(1 To (UBound(arrElements) - LBound(arrElements) + 1) ^ p, 1 To 1)
 
PermutRep 1, "", lRow
Range("A1").Resize(UBound(arrResult)) = arrResult
End Sub
 
Sub PermutRep(ByVal lInd As Long, ByVal sresult As String, ByRef lRow As Long)
Dim i As Long
 
For i = LBound(arrElements) To UBound(arrElements)
    If lInd = p Then
        lRow = lRow + 1
        arrResult(lRow, 1) = sresult & arrElements(i)
    Else
        PermutRep lInd + 1, sresult & arrElements(i), lRow
    End If
Next i
End Sub

Remarks: just change the values of arrElements and p to get other Permutations with repetition

 

[Legacy 14611]

How would I calculate the number of possibilities of something like this?

In the case you outline (using all repeats etc) the total number of possibilities is 3^9, which equals 19683.

Where the 3 and 9 come from should be obvious from your post.

With other combinations the total number can be calculated, but depends on the type of combination you specify.

 

[Gadgetguy]

Thanks a bunch to both of you. pgc01; thank you very much for taking the time to explain to me how to do this type of calculation and taking the time to write even a code to do it; you are awesome!!

rugila; Once again, thank you for all of your help! It's been great to have such a nice support.

It's been a pleasure to post in this forum and I will definitely recommended to other people. I hope I can be of help to others as well so that we can keep this going. Thank you again.

 

[yauchildchew]

I am also trying to develop a combination (not permutation), say building up combinations of 4 elements with {ABC} elements.

Repetition of element usage is allowed so that combinations such as ABCA, AABB, AAAC... are allowed.

However, Repetition of combinations containing the same elements are not allowed; such as ABCA, ACBA, AACB, BCAA,..... all these combinations should only be taken as one combination only.

In this case, what is the proper code?