Is it possible in excel

[Beekman]

Have a series of shifts of varying lengths say between 7.5 to 9.5 hrs long. If I group say 20 or 25 is excel able to sort them over 5 days by mixing them to get as close as possible to 41 hours ( work is calculated to 41 for Mon-Fri). Hope you understand what I'm wanting. There are dedicated programs for this but can excel achieve a similar result?

 

[Boller]

Hope you understand what I'm wanting./QUOTE]

I very much doubt that anyone will.

I think you need to rewrite your question, preferably with an example of what you want to do.

 

[Ruddles]

You have 20 or 25 numbers and you want to divide them into groups of five with each group totalling as near to 41 as possible. That much I understand.

Fairly obviously you need to use VBA. You would have to generate every possible combination and calculate the amount by which the total for each group differs from 41, then store the sum of those differences for each combination. Finally you pick the smallest difference and use the combination which produced it.

I'll give it some thought but I'm definitely not promising anything. If anyone else fancies the mental exercise, please feel free to chip in.

I'll assume 20 numbers at a time to reduce run times.

 

[Ruddles]

No, there are huge numbers involved in generating every possible combination of 20 numbers. The best I can come up with is to 'snake' the numbers from lowest to highest and back again from highest to lowest in each set of five numbers in order to give a combination of values which gets each group as close to the average total as possible.

To test my code, create a new worksheet and place your 20 or 25 numbers in column A starting in row 2, in other words place them in A2:A21 or A2:A26.

Paste this code into a new general code module:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub ShiftPattern()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim ws As Worksheet
  Dim iLast As Integer
  Dim i As Integer
  Dim j As Integer
  Dim iRow As Integer
  Dim iWidth As Integer
  Dim sCol As String
  
  Set ws = ThisWorkbook.Sheets(1)
  ws.UsedRange.Offset(0, 1).ClearContents
  iLast = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  iWidth = (iLast - 1) / 5
  
  If iLast Mod 5 <> 1 Then
    MsgBox vbCrLf & "The input data is not in multiples of five!" & vbCrLf & vbCrLf _
    & "Please adjust the data and re-run this macro." & Space(10), vbOKOnly + vbExclamation
    Exit Sub
  End If
  
  For i = 2 To iLast - 1
    For j = i + 1 To iLast
      If ws.Cells(i, "A") > ws.Cells(j, "A") Then
        ws.Cells(i, "A").Copy ws.Range("B1")
        ws.Cells(j, "A").Copy ws.Cells(i, "A")
        ws.Range("B1").Copy ws.Cells(j, "A")
      End If
    Next j
  Next i
  ws.Range("B1").ClearContents
  
  For iRow = 2 To iLast
    i = Int((iRow - 2) / iWidth) + 2
    j = ((iRow - 2) Mod iWidth) + 3
    If i Mod 2 = 1 Then j = iWidth + 5 - j
    ws.Cells(iRow, "A").Copy ws.Cells(i, j)
    If i = 2 Then
      ws.Cells(1, j) = "#" & CStr(j - 2)
      ws.Cells(1, j).Font.Bold = True
      sCol = Replace(Cells(1, j).Address(1, 0), "$1", "")
      ws.Cells(8, j) = "=SUM(" & sCol & "2:" & sCol & "6)"
      ws.Cells(8, j).Font.Bold = True
    End If
  Next iRow
  
End Sub
[/FONT]

Finally run the code. The shift patterns will be generated starting in column C, so if you have 20 numbers in column A, the patterns will be in columns C-F, if you have 50 numbers in column A, the patterns will be in C-L, etc.

If you want to see the 'snaking' algorithm in operation, place a breakpoint at the final Next iRow statement and step through the code using the F5 (not F8) key whilst you watch the worksheet.

It's not a perfect solution but it's the only one I could come up with which doesn't involve generating horrendous numbers of combinations.

See if that's any good to you.