Permutations of an Array 1

[gal4y]

I have an array with 4 Company's that each of a duration time to complete some task.
Based on some calculations on each of the companies and the amount of subtasks the scheme has various total ending times.

FOr example (Time in minutes)

Company A 15
Company B 17
Company C 20
Company D 16

I have the scheme of how to sort but what I want to do is read in the array (which I have done before) and change the order of the companies (exhaustive) and their associated times and run the calculations. I need to keep track of the array and all its permutations. For this array there are 24 possible variations.

Thank you in advance for your help.

Greg

 

[Zathras]

There is a fairly straight-forward algorithm for permuting the elements of an array, but in this case I believe I would just hard-code a "driver" array of 24 4-character elements and use that to determine the order of processing for each of the 24 cases.

I.e.,
d(1)="1234"
d(2)="1243"
d(3)="1324"
...
d(24)="4321"

(An application of the "kiss" method.)

 

[gal4y]

Zathras,

Because I read the data into the array and it changes from time to time I need a way to make it dynamic. So I would like to hard code it. The other problem I may run into is when it increases to 5 companies or more.

If there is a straight-forward algorithm for permuting the elements of an array, can you please tell me.

Thanks

Greg

 

[Zathras]

Working with an array of numbers that represent the indexes to the "real" array:

1. Start with values in ascending order, left to right.

2. Beginning with right-hand position and working from right to left, find the first value that is less than its right hand neighbor. Call this the "critical position"

3. Find the smallest value currently to the right of the "critical position" (X) that is larger than the value (C) at the "critical position" and exchange values.

4. Sort (ascending) all values to the right of the "critical position"

1 2 3 4 5
      ^ ^
      C X

1 2 3 5 4
    ^   ^
    C   X

1 2 4 3 5
      ^ ^
      C X

1 2 4 5 3
    ^ ^
    C X

1 2 5 3 4
      ^ ^
      C X

1 2 5 4 3
  ^     ^
  C     X

1 3 2 4 5
      ^ ^
      C X

1 3 2 5 4
    ^   ^
    C   X

1 3 4 2 5
      ^ ^
      C X

1 3 4 5 2
    ^ ^
    C X

1 3 5 2 4
      ^ ^
      C X

1 3 5 4 2
  ^   ^
  C   X

1 4 2 3 5
      ^ ^
      C X

etc.

 

[gal4y]

Thank you so much

 

[gal4y]

Zathras,

I tried to code this but based on my experience I am not able to do it.

I have the array and I know how to swap items but not to the level I need to. Can you help me get the framework down.

Thank you

Greg

 

[Zathras]

Here is one way to do it.

Set up a worksheet like this:

   A      B          C
 -----  -----   --------------
1

[blue]

  1      123   Apples

[/color]

2

[blue]

  2      456   Bananas

[/color]

3

[blue]

  3      789   Cherries

[/color]

4

[blue]

  4     1234   Dates

[/color]

5

[blue]

  5     5678   Eggplant

[/color]

[/color]
And put this code into a VBA code module:
[blue]

Option Explicit

Sub Demo()
  If Not Permute("A1:C5") Then
    MsgBox "Last permutation is already showing"
  End If
End Sub

Function Permute(ARangeAddress As String) As Boolean

[green]

' Rearranges a vertical array of numbers into the
' "next" permutation.  Start with 1,2,3,.....
' E.g. if the range contains values 1,2,3,4,5 then
' after execution the range will contain 1,2,3,5,4
' If the input range contains more than one column,
' the numbers must be in the first column.

[/color]

Dim nRowCount As Integer
Dim nColCount As Integer
Dim i As Integer
Dim iCrit As Integer
Dim nCrit As Integer
Dim iSwap As Integer
Dim nSwap As Integer
Dim Temp
Dim rSort As Range
Dim sKey As String
  
  With Range(ARangeAddress)
    nColCount = .Columns.Count
    ReDim Temp(nColCount)
    nRowCount = .Rows.Count

[green]

    ' Find "critical" cell

[/color]

    iCrit = 0
    For i = nRowCount - 1 To 1 Step -1
      If .Cells(i, 1).Value < .Cells(i + 1, 1).Value Then
        iCrit = i
        Exit For
      End If
    Next i

[green]

    ' See if we are at the end

[/color]

    If iCrit <= 0 Then
      Permute = False
      Exit Function
    End If

[green]

    ' Find cell to swap

[/color]

    nCrit = .Cells(iCrit, 1).Value
    nSwap = 999
    iSwap = 0
    For i = iCrit + 1 To nRowCount
      If .Cells(i, 1).Value > nCrit Then
        If .Cells(i, 1).Value < nSwap Then
          iSwap = i
          nSwap = .Cells(iSwap, 1)
        End If
      End If
    Next i

[green]

    ' Swap data between rows iCrit and iSwap

[/color]

    For i = 1 To nColCount
      Temp = .Cells(iSwap, i)
      .Cells(iSwap, i) = .Cells(iCrit, i)
      .Cells(iCrit, i) = Temp
    Next i

[green]

    ' Sort the cells below iCrit

[/color]

    Set rSort = Range(.Cells(iCrit + 1, 1), .Cells(nRowCount, nColCount))
    sKey = rSort.Cells(1, 1).Address(0, 0)
    rSort.Sort Key1:=Range(sKey), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  End With
  
  Set rSort = Nothing
  Permute = True
End Function

[/color]
Every time you run the Demo macro, the data will be changed to reflect the "next" permutation.

 

[gal4y]

Thank you for your help.
I understand a lot more than I did yesterday

Greg

 

[Zathras]

Glad to hear it.

BTW, you should delete the statement

   ReDim Temp(nColCount)

It is left over from an earlier version of the code as I was developing it. It doesn't do anything since Temp is not used an array in the final version of the code. Sorry for any confusion.