# Which Numbers Sum to Target

Download Recursion1.zip

The problem: You have a list of numbers, some of which sum up to another number. If you have 10 numbers, there are 2^10 or 1,024 possible combinations. A pretty daunting task.

Recursion to the rescue. This file allows you to enter a list of numbers and a target, and it will tell you which numbers sum to the target. Naishad Rajani wrote the code and Jimmy Day prettied up the UI. I only have the distinction of distributing the file. I did spend quite a bit of time stepping through this macro about five years ago. I had never used recursion at the time, and it was difficult to get my head around.

A word of warning. Twenty items in the list seems to be about the upper limit from a time perspective. Any more than that and it takes too long to process.

## 64 thoughts on “Which Numbers Sum to Target”

1. Bernie Deitrick says:

Harlan Grove’s procedure kicks the recursive code’s butt.

For what it’s worth, I’ve used the same recursive version for the last 7 years or so – but I think it was originally posted on the web by Michel Claes.

Copy the code below into a code module, and set the references as instructed in the comments.

Then run findsums and highlight the ranges with your values when prompted.

Bernie
MS Excel MVP

Option Explicit
‘Begin VBA Code

Sub findsums()
‘This *REQUIRES* VBAProject references to
‘Microsoft Scripting Runtime
‘Microsoft VBScript Regular Expressions 1.0 or higher

Const TOL As Double = 0.000001 ‘modify as needed
Dim c As Variant

Dim j As Long, k As Long, n As Long, p As Boolean
Dim s As String, t As Double, u As Double
Dim v As Variant, x As Variant, y As Variant
Dim dc1 As New Dictionary, dc2 As New Dictionary
Dim dcn As Dictionary, dco As Dictionary
Dim re As New RegExp

re.Global = True
re.IgnoreCase = True

On Error Resume Next

Set x = Application.InputBox( _
Prompt:=”Enter range of values:”, _
Title:=”findsums”, _
Default:=””, _
Type:=8 _
)

If x Is Nothing Then
Err.Clear
Exit Sub
End If

y = Application.InputBox( _
Prompt:=”Enter target value:”, _
Title:=”findsums”, _
Default:=””, _
Type:=1 _
)

If VarType(y) = vbBoolean Then
Exit Sub
Else
t = y
End If

On Error GoTo 0

Set dco = dc1
Set dcn = dc2

Call recsoln

For Each y In x.Value2
If VarType(y) = vbDouble Then
If Abs(t – y) t Then dcn.Add Key:=”+” & _
Format(v(k, 1)), Item:=v(k, 1)
Next k

On Error GoTo CleanUp
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

For k = 2 To n
dco.RemoveAll
swapo dco, dcn

For Each y In dco.Keys
p = False

For j = 1 To n
If v(j, 3) = rgt) Then Exit Sub
swap2 v, lft, lft + Int((rgt – lft + 1) * Rnd)
pvt = lft
For j = lft + 1 To rgt
If v(j, 1) > v(lft, 1) Then
pvt = pvt + 1
swap2 v, pvt, j
End If
Next j

swap2 v, lft, pvt

qsortd v, lft, pvt – 1
qsortd v, pvt + 1, rgt
End Sub

Private Sub swap2(v As Variant, i As Long, j As Long)
‘modified version of the swap procedure from
‘translated from Aho, Weinberger & Kernighan,
‘”The Awk Programming Language”, page 161

Dim t As Variant, k As Long

For k = LBound(v, 2) To UBound(v, 2)
t = v(i, k)
v(i, k) = v(j, k)
v(j, k) = t
Next k
End Sub

Private Sub swapo(a As Object, b As Object)
Dim t As Object

Set t = a
Set a = b
Set b = t
End Sub
‘— end VBA code —

2. Sige says:

I tried the workbook: Impressive!

Would like to try your code too Dick but is it possible you cannot set the reference to
“Microsoft VBScript Regular Expressions 1.0 or higher”
in XL97?

Brgds Sige

3. Jim T. says:

Bernie graciously sent me a copy of the code from Michael a while ago. I modified it a tad and added it as a button on a custom menu. Here is my code. It basically is the same as Bernies except that it finds all of the solutions and lets the user see the possibles one after the other. Bernies actually show the last of possibly many solutions. Select a range and run the code. It will prompt you for a number. Then just let it rip. If it is taking too long hit escape. If the range is more than 25 elements it warns you that this could take a while…

‘ Original solution created by
‘ Michel Claes

Private Const intWarningElements As Integer = 25
Private dblTargetValue As Double
Private intElements As Integer
Private intCurrentSolutionFlags() As Integer
Private intBestSolutionFlags() As Integer
Private dblElementValues() As Double
Private dblBestSolution As Double
Private rngRangeToSearch As Range
Private blnFound As Boolean

Public Sub FindSolution()
Dim intCounter As Integer
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngRangeToSearch = Intersect(Selection, wks.UsedRange)
If rngRangeToSearch Is Nothing Then Set rngRangeToSearch = ActiveCell
dblBestSolution = 0
dblTargetValue = GetTargetValue
intElements = rngRangeToSearch.Count
ReDim dblElementValues(intElements)
ReDim intBestSolutionFlags(intElements)
ReDim intCurrentSolutionFlags(intElements)

If intElements > intWarningElements Then
If MsgBox(“This may take a VERY long time to execute. Did you wish ” & _
“to proceed?”, vbYesNo + vbCritical, “Proceed???”) = vbYes Then
Call ProcessSelection
End If
Else
Call ProcessSelection
End If

If blnFound = False Then
Call StoreSolution
Else
MsgBox “The search is completed.”, vbInformation, “Complete”
End If
Set wks = Nothing
End Sub

Private Sub ProcessSelection()
Dim intCounter As Integer

Application.StatusBar = “Processing. Please Wait.”
For intCounter = 1 To intElements
On Error Resume Next
dblElementValues(intCounter) = rngRangeToSearch.Item(intCounter)
On Error GoTo 0
Next intCounter
Evaluate 0, 1
Application.StatusBar = False

End Sub

Private Sub StoreSolution()
Dim intCounter As Integer
Dim rngFound As Range

For intCounter = 1 To intElements
If intBestSolutionFlags(intCounter) = 1 Then
If rngFound Is Nothing Then
Set rngFound = rngRangeToSearch.Item(intCounter)
Else
Set rngFound = Union(rngRangeToSearch.Item(intCounter), rngFound)
End If
End If
Next intCounter
If rngFound Is Nothing Then Set rngFound = ActiveCell
rngFound.Select
If Round(Application.Sum(rngFound), 4) Round(dblTargetValue, 4) Then _
MsgBox “An exact match was not found. The closest match is:” & _
vbCrLf & vbCrLf & vbTab & _
Format(Application.Sum(rngFound), “#,##0.00?) & _
vbCrLf & vbCrLf & “A difference of:” & vbCrLf & vbCrLf & vbTab & _
Format(Application.Sum(rngFound) – dblTargetValue, “#,##0.00?)
Set rngFound = Nothing
End Sub

Private Sub CopySolutionFlags()
Dim intCounter As Integer

For intCounter = 1 To intElements
intBestSolutionFlags(intCounter) = intCurrentSolutionFlags(intCounter)
Next intCounter
End Sub

Private Sub Evaluate(ByVal total As Double, ByVal pos As Integer)
On Error GoTo HandleCancel
Application.EnableCancelKey = xlErrorHandler

If pos 0 Then blnInputOk = True
End If

Do While blnInputOk = False
MsgBox “The value entered must be a number not equal to 0. ” & _
“Please try again.”, vbInformation, “Input Error”
strInput = InputBox(“Please enter the target value.”, “Target Value”)
If strInput = Empty Then End
If Not IsNumeric(strInput) Then
If CDbl(strInput) 0 Then blnInputOk = True
End If
Loop
GetTargetValue = CDbl(strInput)
End Function

4. Bernie Deitrick says:

Jim,

Did you have a problem pasting your code? The last sub has an End Function statement rather than an End Sub, so there appears to be a chunk missing.

Bernie

5. Dick Kusleika says:

Bernie or Jim: Can you either of you email the workbook to me. Pasting code in these comments stinks because of the html translation. Thanks.

6. Mike says:

Jim,

Can you you email the workbook to me. I have the same problem as Dick.

Thanks

7. Jim T. says:

Dick, Bernie and Mike. I sent you the file. If you did not get it let me know…

8. Dick Kusleika says:

Got it Jim. I’m planning on posting it for download if nobody has a problem with that.

9. Michael W says:

Dick/Jim T,

Can either of you send me the workbook also, I am having the same problem. The recursion1.zip file will not download for me from the link for some reason.

10. Michael W says:

My email is mawatson64@yahoo.com

11. Scott says:

Can either of you send me the workbook also, I am having the same problem. The recursion1.zip file will not download for me from the link for some reason.

12. Dick Kusleika says:

If you guys can wait until Wednesday, I will have it available for download.

13. Fred Queary says:

Bernie,

It looks like there is an error in the line

If Abs(t – y) t Then dcn.Add Key:=”+” & _

Is there supposed to be something between “(t-y)” and the “t”

14. Fred Queary says:

Bernie,

There is also an issue with

Call recsoln

There is nothing called “recsoln” for it to call??

15. Dick Kusleika says:

Fred: It’s no doubt a greater than or less than sign which WordPress incorrectly interprets as html. Posting code in the comments of this blog stinks and I take full responsibility for it. The ‘recsoln’ I can’t account for.

16. Scott says:

Hello,

I was wondering if the code can be modified slightly to gain all possible combinations that are less than or equal to a target number.

Thanks,
Scott

17. Scott says:

Sorry, I also would like to have a constraint that the combination of numbers always has to contain the same amount of cell references (ie. combinations have to have 11 different cell references (numbers))

Eg. 250 numbers, exactly 11 numbers must be chosen, the sum of these numbers must be less than or equal to 1000.

Thanks,
Scott

18. Zach says:

Is this file available for download? I only see the recursion1.zip. If not, can someone mail it to me at zfraile@gmail.com?

Thanks!

19. OO7-kanwal says:

Hi Dick,

Can we see the Recursion2 available for download. Just love that code.

Regards
kanwaljit

20. Dick Kusleika says:

kanwaljit: I looked for it last night, but couldn’t find it. I’m sure it’s there somewhere. I’ll look again tonight and hopefully get it posted this weekend.

21. Tushar Mehta says:

I don’t know how well behaved Bernie’s post or various revisions of Harlan’s code or any of the variants of the recursive solutions are. My gut instinct would be to ask if any of them follow the developments from the world of Operations Research, specifically, the Simplex method. Luckily, Solver uses just that method when told that the problem is a “linear problem.” For a template, see
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/tips/template-set-match.html

22. OO7-kanwal says:

Hi Dick,

Thanks a million to both of you. I was Delighted to see the reply. Thanks Dick, would love to wait for the file !
Thanks a lot Tushar, I really got mad searching for something to do that. Just saw yours comment. Feels jubilated, just on seeing the link. A link to something on yours site. Must be perfect, I believe. Let me try that.

Best Regards
Kanwaljit

23. OO7-kanwal says:

Hi,

It seems the Recursion file given on this site suits my requirements in a better way. While I am trying my best to make the things work for me, it would help me a lot, If I have a chance to try the Said 2nd Recursion.zip. Dick, Do upload the file, if you can find it. Hopefully.

Thanks and Regards
Kanwaljit

24. Dick Kusleika says:

I haven’t been able to find it, which is surprising since I never delete anything.

25. OO7-kanwal says:

Very unfortunate for me………. What to do now ? Does anyone of the other participants have a copy of it ? If Bernie or Jim or anyone else has saved a copy of it, please request them on my behalf to provide a copy of it.

Thanks and Regards
Kanwaljit

26. Diana says:

Thank you so much. You will save me so much time.

27. Zach says:

I keep this in my custom add-in with a toolbar button to the TargetFinder macro. I basically took the code from the recursion model workbook posted in a prior comment out of the template worksheet and adapted it to work on whatever items you have selected at the moment. I find it very useful when trying to figure out what accounts were rolled up into a total account in financial statements. I wish I could understand Tushar’s code enough to set it up in a similar way. As it stands right now, I can barely run this against 22 numbers on a 2GHz Core2Duo rig before it’s too slow to bear.

Option Explicit
Dim dblTarget As Double
Dim intElements As Integer
Dim intStat() As Integer
Dim intStatb() As Integer
Dim dblElements() As Double
Dim dblBest As Double
Dim rngInputCells() As Range
Sub StoreIt()
Dim intCount As Integer
Dim dblClosest As Double
Dim rngResult As Range

For intCount = 1 To intElements
If intStatb(intCount) = 1 Then
dblClosest = dblClosest + rngInputCells(intCount).Value
If rngResult Is Nothing Then
Set rngResult = rngInputCells(intCount)
Else
Set rngResult = Union(rngResult, rngInputCells(intCount))
End If
End If
Next intCount
If Not rngResult Is Nothing Then rngResult.Select
If dblClosest = 0 Then
MsgBox “An answer could not be found”, vbInformation, “Unsolvable”
Else
If MsgBox(“You searched for “ &amp; Format(dblTarget, “#,###.00”) &amp; “.” &amp; vbCrLf &amp; “The closest answer is “ &amp; Format(dblClosest, “#,###.00”) &amp; “.” &amp; vbCrLf &amp; vbCrLf &amp; “Press Yes to highlight these in red.” &amp; vbCrLf &amp; vbCrLf, vbInformation + vbYesNo, “Result”) = vbYes Then
rngResult.Interior.ColorIndex = 3
End If
End If

End Sub
Sub CopyIt()
Dim intCount As Integer

For intCount = 1 To intElements
intStatb(intCount) = intStat(intCount)
Next intCount

End Sub
Sub Evaluate(ByVal total As Double, ByVal pos As Integer)

If pos  0 Then ‘skip zeros
If Not dblElements(1) = 0 Then ‘don’t redim first time
ReDim Preserve dblElements(1 To UBound(dblElements) + 1)
ReDim Preserve rngInputCells(1 To UBound(rngInputCells) + 1)
End If
dblElements(UBound(dblElements)) = CDbl(rngCell.Value)
Set rngInputCells(UBound(rngInputCells)) = rngCell
End If
End If
Next
If UBound(dblElements) &gt; 20 Then
If MsgBox(“You selected “ &amp; UBound(dblElements) &amp; ” items.  Anything over 20 may take a very long time to calculate.  Are you sure you want to continue?”, vbExclamation + vbYesNo, “Are you sure?”) = vbNo Then Exit Sub
End If
ReDim intStat(1 To UBound(dblElements))
ReDim intStatb(1 To UBound(dblElements))
dblBest = 0
dblTarget = Application.InputBox(“What is the Target amount?”, “Target”, Type:=1)
intElements = UBound(dblElements)

Evaluate 0, 1
StoreIt

End Sub

28. leo says:

Hi,

When searching the internet for my ‘problem’, I found your emailchain that looks exactly what I was looking for. Never realized it would take so much time to come to sollutions for subsets larger than 25 items, I have sets of 100 items and larger… But I couldn’t find the Jim T sollution, I would love to have this code that will show all possible sollutions and not just one possible sollution. Dick Kusleika says it is posted on the intranet, but I cann’t find it back. Can you please send it to me?

By the way I love this excel forum! Realy good.

Thanks,

Leo

29. matthew steele says:

Could one of you guys contact me (matt.j.steele@gmail.com) about the recursion formulas shown in this blog. I am prepared to pay for a excel spreadsheet solution that involves multiple targets from a list of data, using each value in the data list only once. I would also like to receive a copy of the spreadsheet that Dick Kusleika said he would post on internet back in 2005. Thanks.-

30. Tushar Mehta says:

leo, matthew steele: The more recent version of the webpage I linked to above now has a VBA routine that lists multiple combinations, each of which sums to the target value. See
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/templates/match_values/index.html

31. logan says:

question about a code

masinalogan@yahoo.co.uk
Dear Mr .
http://www.developerfood.com/re-how-to-get-all-number-combination-from-a-list/microsoft-public-excel-programming/fe274fbc-1e26-4385-863f-257eaa3a7b18/article.aspx
At this address i found a very nice code.
I put it on Excel and with macro it is working good.
But i want to find the solution at the next 2 problems.If you can , please teach me how to made the code.
For exemple before the code is running i want to make to appear a mesage like this
DO YOU WANT TO SHOW (DISPLAY) ALL COMBINATIONS OR JUST THE COMBINATIONS THAT CONTAIN NUMBER 1 ( or 77 or 84 etc)?

……………………………………………………..

Or another condition:
I want to write some arrays on the excel sheet:
array1 from AC5:DD5 first array
array2 from AC6:DD6 second.
array3 from AC7:DD7
array4 from AC8:DD8
…………………………………………………..
…………………………………………………………….
array n from ACn:DDn
Then i want a code that read one after another this arrays and take only m numbers
from an array.
So the combinations that apearrs must contain only m numbers from this arrays

array1 from AC5:DD5 first array
array2 from AC6:DD6 second.
array3 from AC7:DD7
array4 from AC8:DD8
…………………………………………………..
…………………………………………………………….
array n from ACn:DDn

If you can help me THANK YOU.
If you canot then i must surf the Web.
Thank you for your time and consideration.

32. Kanwaljit says:

Hi Mr. Logan
The above link is not working. Any Guess

33. Lori says:

The posted example can also be solved quickly without code…
In cell C1, ctrl+shift+enter:

=MIN(ABS(
INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^1)/2^0)*B3
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^2)/2^1)*B4
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^3)/2^2)*B5
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^4)/2^3)*B6
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^5)/2^4)*B7
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^6)/2^5)*B8
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^7)/2^6)*B9
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^8)/2^7)*B10
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^9)/2^8)*B11
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^10)/2^9)*B12
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^11)/2^10)*B13
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^12)/2^11)*B14
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^13)/2^12)*B15
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^14)/2^13)*B16
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^15)/2^14)*B17
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^16)/2^15)*B18
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^17)/2^16)*B19
+INT(MOD(ROW(B:B)+2^16*{0,1,2,3},2^18)/2^17)*B20
-B1)*100+(ROW(B:B)+2^16*{0,1,2,3})/10^6)

(or use row(b1:b262144) in xl2007). Now fill down from C3:

=INT(MOD(ROUND(10^6*MOD(\$C\$1,1),0),2^A3)/2^A3*2)

For other solutions you can use SMALL(,k) in place of MIN().

34. Joe B says:

1000 points to you.
I was presented the problem,
tried to solve it myself,
realized it would be difficult to code, (for me, at least)
found the name of the problem,
searched google,
and found you had already created the solver for me!

I’m thrilled, Thanks.

~Joe

35. David J says:

Just downloaded Recursion1.zip and it works perfectly! It’s a little slow with 30 to 40 combinations but all told it’s a life saver. I don’t know about the rest of you but I will be using this when I reconcile my credit card deposits in QuickBooks Pro. For the last 2 years I’ve cussed QB for not having this built in. THANKS!

36. Rebecca says:

THANK YOU SO MUCH! This was an enormous time-saver for me.

37. Martin says:

This is great & I have used it on some smaller ranges, however on bigger ranges the number of possible combinations is extremly large & can be very slow.

For example I may have 200 numbers, but the figure I am looking for is probably made up of a combination of just 5 or fewer of these. How can I get the code just to look at these smaller combinations?

This way I can do a quicker check & if nothing is found, re-run for 10 number combinations, 15 etc, until I find the result.

38. dermotb says:

I think I have a faster solution in cases where you want to limit the number of items in the total, or the total number of possible solutions. It uses a couple of computer chess tricks (like pre-storing a large number of combinations). For example, given a target figure, I can find
* 8 items out of 60 items, in 20 seconds
* 5 items out of 200 items, in under a minute

Obviously, it gets much slower for more items, or where there are many possible solutions, but it should be pretty effective for business solutions.

It can definitely be made more efficient, and it hasn’t been independently checked, because I did it just for fun, so I haven’t fine tuned it, but I’m happy to share it.

Write to me at dermot dot balson at gmail dot com if you’d like a copy

39. Camilla says:

HELP PLEASE :)

I get an error when my list of numbers exceed 30. The list i’m trying to work from has 700 numbers in the list. Has anyone been sucessful using this with a large range?

The error i get is below:
elems(i) = Cells(i + 2, 2)

any help would be greatly appreciated!

thanks

40. PK says:

FYI to future readers

In response to Camilla’s post the number records that can be processed can be increased by changing the length of the variables.

i.e.

instead of:

Global stat(30) AS Integer
Global statb(30) AS Double
Global elems(30) As Double

use:

Global stat(60) AS Integer
Global statb(60) AS Double
Global elems(60) As Double

this will allow you to use up to 60 values in the equation.

NOTE: using this many values will SIGNIFICANTLY increase the time taken to calculate!

41. 0alex0 says:

Thanks a lot; I just needed this macro for a otherwise seemingly unsolvable problem.

Kind regards,
0alex0

42. Santosh says:

It is not giving accurate answer by Benzadeus as Recursion worked.

43. kartik says:

HELP PLEASE

I get an error when my list of numbers exceed 30. The list i’m trying to work from has 700 numbers in the list. Has anyone been sucessful using this with a large range?

The error i get is below:
elems(i) = Cells(i + 2, 2)

any help would be greatly appreciated!

thanks

44. kartik says:

Camilla,

same error i have it. pls. if you have solution pls. let me know

Thanks
Kartik

45. Ajay Aggarwal says:

Dear Chandu,

Whether there is any excel commands also. I do not want to use macro and want to get the target with formula. Please help.

46. lhm says:

Ajay – In Excel 2007+, you can try with CTRL+SHIFT+ENTER in C3:C20 of Dick’s example above :

`=INT(MOD(MOD(SMALL(ABS(ROUND((MMULT(INT(MOD(ROW(A1:INDEX(A:A,2^ROWS(B3:B20)))/2^TRANSPOSE((A3:A20-1)),2)),B3:B20)-B1)*10^9,-7))+ROW(A1:INDEX(A:A,2^ROWS(B3:B20))),{1}),10^7)/2^(A3:A20-1),2))`

This takes only around a second to recalculate if the values are changed and the size of the range can be adjusted up to 20 values. You can also change the {1} in the formula to {1,2} and array-enter in multiple columns to get the two closest combinations of values.

Note: You first need to save the posted file in XLSM format and open in a new session to view the larger grid.

47. KARTIK says:

benzadeus,

PLS. HELP ME ABOUT VLOOKUP FUNCTION IN YOUR PROJECT. EXP. A : B RESULT GIVE ME INTO C : D

THANKS
KARTIK

48. KARTIK says:

Dear Sir,

There are a set of debit values and a set ot credit values in a column. I want a vba code by whcich the debit value plus a single / multiple credit value is zero that needs to be marked .

finally i will come to know out of the avaibale debits which cannot be used the with avilable credits either single or multiple values.

If multiple matching sets are available let it take the 1st or the 2nd one its not an issue.

Column A Ref

-1000 A
-5000 B
-8000 C

800 A
100 A
100 A
2000 B
3000 B
13000
15000

Thanks in Advance

49. KARTIK says:

Dear benzadeus,

I have fond code but hot to oprate in excel pls. let me know,

Sub MG11Apr43
Dim Rng As Range, Dn As Range
Dim Deb As Double
Dim Cred As Double
Dim col As Integer
Dim Q As Variant
Dim Ray As Variant
Dim Del As Integer
Set Rng = Range(Range(“B2”), Range(“B” & rows.Count).End(xlUp))
With CreateObject(“scripting.dictionary”)
.CompareMode = vbTextCompare
For Each Dn In Rng
If Dn.Offset(, -1) 0 Then
Cred = Dn.Offset(, -1)
End If
If Not .Exists(Dn.value) Then
.Add Dn.value, Array(Dn.Address, Deb, Cred, “”)
Else
Q = .Item(Dn.value)
If Dn.Offset(, -1) > 0 Then
Q(2) = Q(2) + Dn.Offset(, -1)
Else
Q(1) = Dn.Offset(, -1)
End If
If Q(3) = “” Then
Q(3) = Q(3) & Q(0) & “,” & Dn.Address
Else
Q(3) = Q(3) & “,” & Dn.Address
End If
If Q(1) + Q(2) = 0 Then
col = col + 1
Ray = Split(Q(3), “,”)
For Del = 0 To UBound(Ray)
Range(Ray(Del)).Offset(, 1) = col
Next Del
End If
.Item(Dn.value) = Q
End If
Deb = 0: Cred = 0
Next
End With
End Sub

Thanks

50. chavan says:

I’m trying to rewrite this code in php, Can you please advice me the Data flow. so I can convert this. I tried lots of combination, it gets me a Memory exceeded error.

51. Dick Kusleika says:

If you’re running out of memory, it’s most likely because you never get out of the recursion. The program flow is recursive, so explaining it here won’t help. The ‘eval’ procedure calls itself. You need to make sure that eval has a path where it doesn’t call itself anymore (if pos>nbr_elem, eval stops calling itself).

52. kartik says:

Dear all,

I have find minimum value into sheet1 a1,sheet2 a1, sheet3 a1, sheet4 a1. result is ref. a2 (in sheet 1 to 4)

Thanks
Kartik

53. srini says:

Hi,

Please help me to find some solution for the below.

I have 200 row’s which consist Positive values & Negative values. I would like to find which are all making a target value. example: My target value is -10 and the result should be which are the item’s making up this total.

-1
2
3
-4
-6
5

54. Dick Kusleika says:

If you had Excel 64-bit and a Cray supercomputer, it would probably still take a month to do that with 200 rows. On my machine, sum to target takes the following times:

 Number of Items Time in Seconds 10 1 20 4 25 126 30 9200

That last one is bout 2.5 hours. Those are max times.

It think you need to find a different program or, better yet, a different approach than Excel.

55. Pingback: concrete sum
56. Catpol says:

Srini, we have an algorithm for this kind of problems. It is VERY FAST. It will be released as SumMatch Pro in a few months time.
For now I am happy to try it on your numbers. I need you to post the numbers and the target value.

57. Tim Cooley says:

Is there an update on this? It seems like the most referenced site, but the zip file at the top is not active.

58. Dick Kusleika says:

Link fixed.

59. Ginrikisha says:

Hi – wondering if there is code to only output the results that sum four of the numbers in the range (or other such specified number), that sum to the specified total? Instead of getting all possible combinations.

thanks

60. Dick Kusleika says:

@Ginrikisha Here’s a version that let’s you define the max number of elements

http://dailydoseofexcel.com/excel/SumToTargetMax.zip

61. umashankar says:

good,

but not working with any amount of sum

Posting code? Use <pre> tags for VBA and <code> tags for inline.