making a list of PO combinations that equal X using list of random numbers

Question

I have an excel sheet that has a list purchase orders with a case value associated with each PO. for example:

3156922129  10
756941986   13
1356902364  15
3156892373  15
9156912182  17
3756902389  22

I don't have the full list posted but it has 42 instances of the above example.

What we are having to manually now is look at the PO quantities and get combinations that add up tp 75. This is the number of boxes we can fit on a pallet. At the end we end up with 26 skids with the PO's that equal to or come close to the 75 pc goal. Is there a way to take this list and have excel give you what PO's would equal to or come close to the desired 75 goal?

Answer 1

With your posted data in columns A and B, running this short macro:

Sub kombo()
    Dim N As Long, i As Long, nBits As Long
    Dim s As String, j As Long, zum As Long

    nBits = 6
    N = 2 ^ nBits - 1

    For i = 1 To N
        s = Application.WorksheetFunction.Dec2Bin(i, nBits)
        For j = 1 To nBits
            Cells(j, 3) = Mid(s, j, 1)
        Next j
        zum = Evaluate("SUMPRODUCT((B1:B6)*(C1:C6))")
        If zum = 75 Then Exit Sub
    Next i

End Sub

will count in binary from 1 to 63, writing patterns of 0's and 1's into column C. Each pattern represents a sub-set of purchase orders. Each sub-set is summed up. When the sum hits 75, the program halts.

This is demo code written for only 6 items. To handle 42 items, the code needs to be modified to generate the larger 42 bit pattern-set and the much larger loop variables.

EDIT#1:

I updated the code, but only tested it with 12 items. The code becomes VERY slow as items are added (unless you get lucky). The code uses a function posted by Rick Rothstein here:

MrExcel Post

Sub kombo_2()
    Dim n As Double, i As Double, nBits As Long
    Dim s As String, j As Long, zum As Long

    nBits = 12
    n = 2 ^ nBits - 1
    Range("C:C").ClearContents
    Application.ScreenUpdating = False
    For i = 1 To n
        s = DecToBin(i, nBits)
        For j = 1 To nBits
            Cells(j, 3) = Mid(s, nBits - j + 1, 1)
        Next j
        zum = Evaluate("SUMPRODUCT((B1:B100)*(C1:C100))")
        If zum = 75 Then Exit For
    Next i
    Application.ScreenUpdating = True
End Sub

'
' https://www.mrexcel.com/forum/excel-questions/578667-use-dec2bin-function-visual-basic-applications-edit-macro.html
'
 'The DecimalIn argument is limited to 79228162514264337593543950245
' (approximately 96-bits) - large numerical values must be entered
' as a String value to prevent conversion to scientific notation. Then
' optional NumberOfBits allows you to zero-fill the front of smaller
' values in order to return values up to a desired bit level.

Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant) As String
  DecToBin = ""
  DecimalIn = CDec(DecimalIn)
  Do While DecimalIn <> 0
    DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & DecToBin
    DecimalIn = Int(DecimalIn / 2)
  Loop
  If Not IsMissing(NumberOfBits) Then
    If Len(DecToBin) > NumberOfBits Then
      DecToBin = "Error - Number too large for bit size"
    Else
      DecToBin = Right$(String$(NumberOfBits, "0") & DecToBin, NumberOfBits)
    End If
  End If
End Function