making a list of PO combinations that equal X using list of random numbers
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?
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