Creating a chart for all possible combinations INCLUDING repetition

[agreer8200]

Hi there,
I've been looking online for a solution and I have not been able to find what I'm looking for thus far.
I'm working on a project and I need items A-E in various placements of 1-5
There can only be 1 item to 1 placement in every set but I need multiple sets for every possible combination of this which means there is repetition.
Is there anyway to accomplish this or would I need to make a chart without repetition for each letter with each number?

 

[Eric W]

Welcome to the MrExcel forum!

There are many ways to create combinations. Could you be more specific in what you want? For example, with 3 items A,B,C there are 6 combinations using each one once:

ABC
ACB
BAC
BCA
CAB
CBA

But if you allow each to be used more than once, there are 27:

AAA
AAB
AAC
ABA
ABB
ABC
ACA
. . .
CCA
CCB
CCC

Is what you want one of these, or some other variation? Could you show an example of how you want your results to look? Consider using the XL2BB tool in the link in the reply box.

 

[agreer8200]

Hi, sorry for not being more clear.
It would be something along the lines of
ABCDE
ABCED
ABDCE
so on and so forth. I want repetition in terms of each line CAN have a letter in the same spot as the previous but there cannot be any repeating items in each line.

 

[Eric W]

Here's a macro that solves that for you. If you're not familiar with macros, here's how to use it. Open a new workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code in the window that opens:

Sub Permutations()
Dim str1 As String, PResults As Object

    str1 = "ABCDE"
    Call Perms(str1, PResults)
    Sheets("Sheet1").Range("A2").Resize(PResults.Count) = WorksheetFunction.Transpose(PResults.keys)
    
End Sub

Sub Perms(st1 As String, PR As Object)
Dim PR2 As Object, i As Long, x As Variant

    Set PR = CreateObject("Scripting.Dictionary")
    If Len(st1) = 1 Then
        PR.Add st1, 1
        Exit Sub
    End If
    
    For i = 1 To Len(st1)
        Call Perms(Left(st1, i - 1) & Mid(st1, i + 1), PR2)
        For Each x In PR2
            PR.Add Mid(st1, i, 1) & x, 1
        Next x
    Next i

End Sub

Change the ABCDE to a string you want to use. Then you can put the cursor on the ABCDE line and press F5 to run it. Or go back to Excel and press Alt-F8 to open the macro selector, select Permutations and click Run.