Solver combinations sum=0

[savosean]

I was wondering if anyone knows a work around for the Solver add-in in Excel to use the sum value as 0... my function works for other numbers that I try but never completes correctly when it comes to finding combinations of my values that equate to 0.

Example that I am using is...

A1=65,A2=34,A3=-34,A4=33
in cell B5 I have =SUMPRODUCT(B1:B4,A1:A4)

In solver my target cell is B5, my variable rance is B1:B4 and in constraints I have B1:B4 binary.

Any way to make this work with finding combinations that sum to 0? I had also found a VBA code online by Tushar Mehta that also didn`t work when trying to find the combinations that have a sum of 0.

Option Explicit

Function RealEqual(A, B, Epsilon As Double)
    RealEqual = Abs(A - B) <= Epsilon
    End Function
Function ExtendRslt(CurrRslt, NewVal, Separator)
    If CurrRslt = "" Then ExtendRslt = NewVal _
    Else ExtendRslt = CurrRslt & Separator & NewVal
    End Function

Sub recursiveMatch(ByVal MaxSoln As Integer, ByVal TargetVal, InArr(), _
        ByVal CurrIdx As Integer, _
        ByVal CurrTotal, ByVal Epsilon As Double, _
        ByRef Rslt(), ByVal CurrRslt As String, ByVal Separator As String)
    Dim I As Integer
    For I = CurrIdx To UBound(InArr)
        If RealEqual(CurrTotal + InArr(I), TargetVal, Epsilon) Then
            Rslt(UBound(Rslt)) = (CurrTotal + InArr(I)) _
                & Separator & Format(Now(), "hh:mm:ss") _
                & Separator & ExtendRslt(CurrRslt, I, Separator)
            If MaxSoln = 0 Then
                If UBound(Rslt) Mod 100 = 0 Then Debug.Print UBound(Rslt) & "=" & Rslt(UBound(Rslt))
            Else
                If UBound(Rslt) >= MaxSoln Then Exit Sub
                End If
            ReDim Preserve Rslt(UBound(Rslt) + 1)
        ElseIf CurrTotal + InArr(I) > TargetVal + Epsilon Then
        ElseIf CurrIdx < UBound(InArr) Then
            recursiveMatch MaxSoln, TargetVal, InArr(), I + 1, _
                CurrTotal + InArr(I), Epsilon, Rslt(), _
                ExtendRslt(CurrRslt, I, Separator), _
                Separator
            If MaxSoln <> 0 Then If UBound(Rslt) >= MaxSoln Then Exit Sub
        Else
            'we've run out of possible elements and we _
             still don't have a match
            End If
        Next I
    End Sub

Function ArrLen(Arr()) As Integer
    On Error Resume Next
    ArrLen = UBound(Arr) - LBound(Arr) + 1
    End Function

Sub startSearch()
    'The selection should be a single contiguous range in a single column. _
     The first cell indicates the number of solutions wanted.  Specify zero for all. _
      The 2nd cell is the target value. _
      The rest of the cells are the values available for matching. _
      The output is in the column adjacent to the one containing the input data.
    Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As Integer
    StartTime = Now()
    MaxSoln = Selection.Cells(1).Value
    TargetVal = Selection.Cells(2).Value
    InArr = Application.WorksheetFunction.Transpose( _
        Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)
    ReDim Rslt(0)
    recursiveMatch MaxSoln, TargetVal, InArr, LBound(InArr), 0, 0.00000001, _
        Rslt, "", ", "
    Rslt(UBound(Rslt)) = Format(Now, "hh:mm:ss")
    ReDim Preserve Rslt(UBound(Rslt) + 1)
    Rslt(UBound(Rslt)) = Format(StartTime, "hh:mm:ss")
    Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
        Application.WorksheetFunction.Transpose(Rslt)
    End Sub

 

[fhqwgads]

tush's code works for me
are you adding the two cells above your data? the code uses the first two numbers in your selection to define the number of desired results which is all if set to 0, and the sum you want the numbers to add to which is the other 0 in A2. it returns numbers 2 and 3 which are the second and third numbers in your list of numbers: 34 and -34

Book1
	A	B
1	0	0, 11:41:30, 2, 3
2	0	11:41:30
3	65	11:41:28
4	34
5	-34
6	33
Sheet14

whenever i use this code i like to have another column next to my list which numbers the numbers so i can find them more easily
here's an example using some randomly generated numbers. my desired number of sums is 20 and the total i'm looking for is 40

Book1
	G	H	I
12		20	40, 11:52:21, 1, 2, 3, 5, 6, 12, 18
13		40	40, 11:52:21, 1, 2, 3, 6, 8, 13, 17
14	1	8.44	40, 11:52:21, 1, 2, 3, 7, 13
15	2	5.46	40, 11:52:21, 1, 2, 4, 5, 6, 8, 12, 17
16	3	9.65	40, 11:52:21, 1, 2, 4, 5, 7, 12
17	4	9.32	40, 11:52:21, 1, 2, 5, 6, 7, 8, 11, 14, 16
18	5	0.07	40, 11:52:21, 1, 2, 5, 6, 8, 12, 13, 20
19	6	4.57	40, 11:52:21, 1, 2, 5, 6, 8, 13, 16, 17, 18, 19
20	7	7.48	40, 11:52:21, 1, 2, 5, 7, 8, 9, 11, 16, 20
21	8	2.03	40, 11:52:21, 1, 2, 5, 7, 10, 17, 19, 20
22	9	8.25	40, 11:52:21, 1, 2, 5, 7, 13, 16, 18, 19
23	10	9.46	40, 11:52:21, 1, 2, 8, 9, 15, 16, 17, 19
24	11	7.02	40, 11:52:21, 1, 2, 8, 11, 15, 16, 17, 19, 20
25	12	9.23	40, 11:52:21, 1, 2, 9, 10, 14, 16, 17, 18
26	13	8.97	40, 11:52:21, 1, 2, 10, 11, 14, 16, 17, 18, 20
27	14	4.91	40, 11:52:21, 1, 2, 11, 12, 13, 17
28	15	7.94	40, 11:52:21, 1, 3, 5, 8, 11, 14, 16, 17, 19
29	16	0.02	40, 11:52:21, 1, 3, 5, 13, 14, 15, 16
30	17	0.88	40, 11:52:21, 1, 3, 6, 9, 17, 19, 20
31	18	2.58	40, 11:52:21, 1, 3, 6, 10, 16, 17, 19
32	19	6.98	11:52:21
33	20	1.23	11:52:21
pivot

 

[savosean]

Thank you for your help, that helper column was really helpful in making it easier to understand what was going on. I have one more question maybe you have the answer, is there anyway to expand on the code and make it so the VBA code would be able to identify a USER ID with each amount? as in only verify if things equal 0 within a certain user ID, and then continue on with more amounts associated with other USER ID's.

for example :

111 -34
111 +34
111 23
111 -12
112 +35
112 - 12
112 -35
112 -45

Have the code know that one combinations in user ID 111 can be zeroed out, and subsequently one combination in user ID 112 can be zeroed out?

 

[fhqwgads]

no idea sorry. i dont really know the details on how the code works, just that it does work

 

[savosean]

Does any one know of a way to improve the processing of Tushar Mehta code? It is doing everything I need it to do, but the problem is when I have many line items the code is crashing excel. I have tried googling ways to improve VBA code, and nothing has seems to improve it.