Permutations & Goal Seek

[dkubiak]

I have 7 different groups that I need to arrange in the most efficient order. The efficiency model is already built, and I have 7 cells where I input the order. Then, I have one cell that displays the efficiency.

Since I cannot use a group more than once, I have 5040 permutations:

7*6*5*4*3*2*1 = 5040

I would like to use "Goal Seek" to pick the most efficient permutation.

I have half of an idea:

List the 5040 permutations with an ID column (1, 2, 3...). Then, have cell where I choose the permutation I want by entering the ID. The input cells for the order then use the ID to select the appropriate group (Vlookup?)

Goal seek then uses the ID cell (where i enter the one I want) and the efficiency output.

Any thoughts on this idea?

 

[dkubiak]

Also, is there a permutation generator for excel?

 

[shg]

Here's a simple one:

Option Explicit
Dim asPerm() As Variant
Dim iRow As Long
 
Sub test()
    Const sInp      As String = "1234567"
    With WorksheetFunction
        If .Fact(Len(sInp)) > Rows.Count Then Exit Sub
        ReDim asPerm(1 To .Fact(Len(sInp)))
 
        iRow = 0
        GetPermutations "", sInp
 
        Columns(1).ClearContents
        Range("A1").Resize(UBound(asPerm)) = .Transpose(asPerm)
    End With
End Sub
 
Sub GetPermutations(sCAR As String, sCDR As String)
    Dim i           As Long
    Dim j           As Long
 
    j = Len(sCDR)
    If j Then
        For i = 1 To j
            GetPermutations sCAR & Mid(sCDR, i, 1), _
                            Left(sCDR, i - 1) & Right(sCDR, j - i)
        Next i
    Else
        iRow = iRow + 1
        asPerm(iRow) = sCAR & sCDR
    End If
End Sub

It lists the permutations to A1:A5040.