#### Finding Cells that Total a Value

```Hello Friends,

I need some assistance in solving a problem.  I have a spreadsheet with
over five hundred lines of transactions. The sum of these transactions
are creating a balance on the account. Is there any formula/macro that
will help me find the transactions creating the balance? The sum of the
account should be zero.  To clarify, if we owe client money, there
would be a transaction setting up that postive balance then a payment
on the account taking it back to zero. There could be multiple
transactions and then one net payment. Or we could be due to receive.
So at the end of the day, the account should be zero. I need to locate
the transactions that are creating the balance (each line is a
transaction).   I need a function to cycle through the cells to try and
identify possible entries that could cause the specific deviation. It
could be a single entry that equals the deviation, or it could be 5
separate cells that when summed up equal the deviation. I would need to
setup the depth of the search, and then construct a table of all of the
possible solutions.   Anyone know how I can do this?

```
 0
JLKaye (1)
8/17/2006 3:48:18 AM
excel 39879 articles. 2 followers.

1 Replies
916 Views

Similar Articles

[PageSpeed] 55

```      Find numbers that add up to a specified sum.
Niek Otten
5-Apr-06

This type of application tends to be very resource-consuming. It is wise to test a solution first with a limited
set of data
One option is using Solver; I include an example given by MVP Peo Sjoblom. The other is a rather famous VBA Sub by Harlan
Grove. There seems to be one flaw: if the table is sorted ascending and the first n numbers sum up to the required value exactly,
it will miss that combination. I don't know if this has been corrected later.
Note the requirements for your settings documented in the code itself

Peo's solution:
==================================================
One way but you need the solver add-in installed (it comes with
put the data set in let's say A2:A8, in B2:B8 put a set of ones {1,1,1 etc}
in C2 put 8, in D2 put
#VALUE!
select D2 and do tools>solver, set target cell \$D\$2 (should come up
automatically if selected)
Equal to a Value of 8, by changing cells \$B\$2:\$B\$7, click add under  Subject
to the constraints of:
in Cell reference put
\$B\$2:\$B\$7
from dropdown select Bin, click OK and click Solve, Keep solver solution
and look at the table
2         1
4         0
5         0
6         1
9         0
13       0
there you can see that 4 ones have been replaced by zeros and the adjacent
cells to the 2 ones
total 8
--
Regards,
Peo Sjoblom
==================================================
Harlan's solution:

'Begin VBA Code

' By Harlan Grove

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) < TOL Then
recsoln "+" & Format(y)

ElseIf dco.Exists(y) Then
dco(y) = dco(y) + 1

ElseIf y < t - TOL Then

c = CDec(c + 1)
Application.StatusBar = "[1] " & Format(c)

End If

End If
Next y

n = dco.Count

ReDim v(1 To n, 1 To 3)

For k = 1 To n
v(k, 1) = dco.Keys(k - 1)
v(k, 2) = dco.Items(k - 1)
Next k

qsortd v, 1, n

For k = n To 1 Step -1
v(k, 3) = v(k, 1) * v(k, 2) + v(IIf(k = n, n, k + 1), 3)
If v(k, 3) > 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) < t - dco(y) - TOL Then Exit For
x = v(j, 1)
s = "+" & Format(x)
If Right(y, Len(s)) = s Then p = True
If p Then
re.Pattern = "\" & s & "(?=(\+|\$))"
If re.Execute(y).Count < v(j, 2) Then
u = dco(y) + x
If Abs(t - u) < TOL Then
recsoln y & s
ElseIf u < t - TOL Then
c = CDec(c + 1)
Application.StatusBar = "[" & Format(k) & "] " & _
Format(c)
End If
End If
End If
Next j
Next y

If dcn.Count = 0 Then Exit For
Next k

If (recsoln() = 0) Then _
MsgBox Prompt:="all combinations exhausted", _
Title:="No Solution"

CleanUp:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = False

End Sub

Private Function recsoln(Optional s As String)
Const OUTPUTWSN As String = "findsums solutions"  'modify to taste

Static r As Range
Dim ws As Worksheet

If s = "" And r Is Nothing Then
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets(OUTPUTWSN)
If ws Is Nothing Then
Err.Clear
Application.ScreenUpdating = False
Set ws = ActiveSheet
r.Parent.Name = OUTPUTWSN
ws.Activate
Application.ScreenUpdating = False
Else
ws.Cells.Clear
Set r = ws.Range("A1")
End If
recsoln = 0
ElseIf s = "" Then
recsoln = r.Row - 1
Set r = Nothing
Else
r.Value = s
Set r = r.Offset(1, 0)
recsoln = r.Row - 1
End If
End Function

Private Sub qsortd(v As Variant, lft As Long, rgt As Long)
'translated from Aho, Weinberger & Kernighan,
'"The Awk Programming Language", page 161

Dim j As Long, pvt As Long

If (lft >= 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 ----
==================================================
================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow
these steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now
in the Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and
then press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello Friends,
|
| I need some assistance in solving a problem.  I have a spreadsheet with
| over five hundred lines of transactions. The sum of these transactions
| are creating a balance on the account. Is there any formula/macro that
| will help me find the transactions creating the balance? The sum of the
| account should be zero.  To clarify, if we owe client money, there
| would be a transaction setting up that postive balance then a payment
| on the account taking it back to zero. There could be multiple
| transactions and then one net payment. Or we could be due to receive.
| So at the end of the day, the account should be zero. I need to locate
| the transactions that are creating the balance (each line is a
| transaction).   I need a function to cycle through the cells to try and
| identify possible entries that could cause the specific deviation. It
| could be a single entry that equals the deviation, or it could be 5
| separate cells that when summed up equal the deviation. I would need to
| setup the depth of the search, and then construct a table of all of the
| possible solutions.   Anyone know how I can do this?
|

```
 0
nicolaus (2022)
8/17/2006 6:55:58 AM

Similar Artilces:

Retrieve Picklist Values from caseOrigin
Hi all, Im attempting to retrieve lookup values for "caseorigincode" contained in the incident entity. If someone has a sample of how to do this in Vb.net, it would greatly be appreciated! Thanks, Larry B. ...

Once user is created in active directory, it cannot find name in o
I have recently had to create a new user in Active directory and for some time it has been working well but today it seems that I cannot link the new user to Outlook. It keeps coming up with an error saying that "the name could not be matched in the list" I have had this problem in the past but was told to update the recipient list, which I have done but it still does not seem to replicate. I know the account exists because it is in our distribution lists but not in the global list or when trying to link to the server. I understand that it may take time to replicate but I am...

find record using combo box
I want to use combo box to locate a name. After update, the combo box shows the name but the form does not move to the record of combobox. Here is my code: Private Sub cboLook_AfterUpdate() Dim rs As DAO.Recordset 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[name]= """ & Me.cboLook & """" 'Display the found record in the form. Me.Bookmark = rs.Bookmark frmFlexSubform.SetFocus Set rs = Nothing End Sub You have a field named Name? Access ...

Save copy of active sheet
I use the code below to save a copy of my Workbook in a specific file path (testing to see if the folders already exist along the way) with a specific, data dependent file name. It works, but I'd like to tweak it a bit and I'm not sure how to write the code. I'd like to save only the active sheet instead of the whole workbook. I'd also like to save only the values and formats to the new file, not the formula and macros. Anyone have any advice on how to accomplish this? Here's my current code: Sub SaveName() If Not Len(Dir("g:\users\one\" & Range("h6&...

Exchange Calendar update tool = unable to find mailbox timezone:Error 0x80004005
When i run the exchange calendar update tool i rececive the following message "Unable find mailbox timezone:Error 0x80004005". I have given my user account full permissions to each exchange store. What else could be causing this problem? alos how do i grant exchange view only admin, exchange full admin, and send as rights to my user account manullay. This script is not working for me? thanks "Jef A" <jef.aldrich@tatumllc.com> wrote in message news:%23AssN4YYHHA.4008@TK2MSFTNGP05.phx.gbl... > When i run the exchange calendar update tool i rececive the follow...

Testing for an error in a range of cells...
I searched through the previous posts on this topic, but have almost no experience using VBA in excel, so I was wondering if there was a simpler way to achieve this: I want to write a formula in B2 that will basically say, "If the range of cells B3 to B24 contains an error, return x if true, and return y if false." I know how to do this for a specific cell rather than a range, for example (=IF(ISERROR(B2),"x","y"). Any help in achieving this without delving into VBA would be much appreciated (I just don't have the time to start learning this stuff as I am w...

Scan a coupon value into RMS
Is there an add-on that will allow you to scan a coupon and decode the value of the coupon and enter it into RMS? Jay ------=_NextPart_0001_83041615 Content-Type: text/plain Content-Transfer-Encoding: 7bit Good morning Jay, Thank you for posting your coupon question. I have not heard of any add-ons that would do this; and would recommend that you enter this as a Product Suggestion: http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en -us/wizard.mspx?dg=microsoft.public.pos&lang=en&cr=US&type=suggestion Todd Berger Microsoft Online Support Get ...

Cannot insert value null into column 'Doctynam',Table tempdb
When trying to invoice a Purchase order we are getting the error Cannot Insert the value NULL into the column 'DOCTYNAM', table 'tempdb.dbo.#PODetailed_______ Column doe Have you tried stopping and starting the SQL Server service? If doing so does not fix the problem, check to see if you have any customizations. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Taff" wrote: > When trying to invoice a Purchase o...

Label a Percentage Bar with Value
This has to be simple... With Excel 2003, I have a cluster column chart with bars comparing the percentage of total projects assigned (red bar) with the percentage of projects completed (green bar) for various employees. So the bars represent percentages, but I'd like to label the "Total" bar with the actual number of projects. I'm sure this can be done; I remember doing it myself long ago. Many thanks If I understand correctly: right click a Total bar on the chart on the shortcut menu use Format Data Series open the Data Label tab and check the Value box best wishes...

Combine Text from Multiple Cells
How do I combine text from mutliple cells into one cell? Hi Carter one option - well two really: if the information is in cells A1, B1 & C1 and you want it combined into D1 then in D1 type =A1 & " " & B1 & " " & C1 this will give you the contents of each with a space between OR =CONCATENATE(A1," ",B1," ",C1) which will give you exactly the same thing Hope this helps Cheers JulieD "Carter" <anonymous@discussions.microsoft.com> wrote in message news:5fc401c48ac0\$9cc86830\$a301280a@phx.gbl... > How do I combine tex...

where can I find the database schema?
and further info about what happens when a transaction occurs? I downloaded the sdk but didnt' see much in there regarding this. Anyone know? Thanks Hi Not sure you can, w/o the customisation guide - which is a closely guarded secret. If you are a strong database person the best thing to do is to run SQL Profiler that comes with SQL Server 2000. It will log pretty well everything that happens. You still will have to figure out the schema yourself but you will see what is going into what tables. JPH On Thu, 15 Feb 2007 04:59:39 -0700, D <Dave@nothing.net> wrote: ...

Hi All If I have Data in cells A1 to A10 or C1 to C23 etc and I want to paste more Data along side, to B1 to B10 or D1 to D23. Even better would be if I could paste to either side of the data. Is there away to do this without having to highlight those cells, this would save me allot of time. Thanks in Advance Dave, When you paste, it pastes the amount of stuff you've copied. If you've copied 10 vertical cells, that's what it's gonna paste. You need only select the top-left-most cell before you do a paste. -- Earl Kiosterud mvpearl omitthisword at verizon period ne...

Invisible Formula in Cells !!!!!!!!!!!!!!!!!!
I have this strang excel file. When I enter formular into it, it don't show thae value but when I enter anything else in the cell, that is visible. The formula is in the cell, its just not visible. On Apr 21, 7:41=A0am, Ayo <A...@discussions.microsoft.com> wrote: > I have this strang excel file. When I enter formular into it, it don't sh= ow > thae value but when I enter anything else in the cell, that is visible. T= he > formula is in the cell, its just not visible. Probably the sheet is protected and Hidden check box of Protection tab of Format Cell dial...

how do I free units cells?
I try to put in alphabetical order a column but appear a messagge about cells joined togheter. How can I free the whole page ? thanks and sorry for my english Try selecting the whole sheet Ctrl A then Format Cells Alignment and make sure the merge cells box is unticked. then try your sort Reagards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107 View this thread: http://www.excelforum.com/showthread.php?threadid=513559 ...

Finding the Last Value in a Range
Excel 2000 I have the following data: Cell Value A1 5055 A2 5047 A3 5044 A4 A5 5039 A6 5037 A7 5032 A8 5028 A9 5026 A10 A11 5019 A12 5014 A13 5017 A14 5021 A15 A16 A17 A18 A19 A20 A21 Formula The column starts out empty and the values are entered one day at a time. The formula at the bottom of the column needs to always return the value in the last non-blank cell in the A1..A20 range. For example, after the first day, when only cell A1 is filled in, the formula s...

find values by cross columns and rows
I need count values, crossing rows when match in columns values. Take a look at the Countif( ) and SumIf( ) functions. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "FCruz" wrote: > I need count values, crossing rows when match in columns values. ...

cell won't wrap text
Very simple Excel file. 3rd column over is is formatted as Text | Wrap Text. All other cells seem to wrap text fine. One cell in particular won't wrap text. It displays all XXXX instead. I did an Edit|Clear and re-entered the text and it did it again. I adjust the height of the cell to no avail. 70+ other cells in this SS work just fine with the same amount of text. Any thoughts? Try changing the format from Text to General -- Gary''s Student "GrHopp" wrote: > Very simple Excel file. 3rd column over is is formatted as Text | Wrap Text. > All othe...

Format a cell to display as all caps
Is there any way to format a cell so that text entered will always display in caps? Hi Not Directly !......but you can use the UPPER function. If your data is in A1, put =UPPER(A1) in A2. This will convet whatever you type in A1 to Uppercase in A2 HTH Michael "WAF" wrote: > Is there any way to format a cell so that text entered will always display in > caps? No. You cannot format a cell to display caps. You can use event code in the worksheet to change the text to caps when you enter it. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column &g...

Extract data in the right part of a cell
I have some cells contains "*" , I want to extrat the text to the right of the "*". For example, if A2 contains "txt*123", I want it turn out to be "123". Thanks. Try the following formula, =MID(A1,FIND("*",A1)+1,LEN(A1)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "plumstone" <plumstone@discussions.microsoft.com> wrote in message news:731CB03C-AD8F-43D8-BFAB-6AD50DEBE799@microsoft.com... > I have some cells contains "*" , I want to extrat the text to th...

Finding Values in a Column Matching to a different workbook
I am having trouble with a report... Example: A workbook of all the clients for the month and a workbook of zip codes in High Risk areas. I need a quick solution to test if the monthly clients have a zip code in the list of High risk zip codes. There are over 500 clients to look up and currently I've been using the Control-F function to see if the zips are in the list and it is very time consuming! Please let me know if you all have any suggestions. Thanks! -- Diana Add a new row 1. Type the zipcode you're searching for in A1. Then in B1, put this formula: =countif(a3...

my outlook address book can't find my contacts
I am using XP professional with Outlook 2002 1/ I have loads of email addresses stored in the email address field of my contacts database. I have for example 4 entries for 'Joe' Joe Soap, Joe Smith, Joe Belushi, Joe Cohen. When I click new mail and type the word Joe in the to: field and click send, my address book cannot automatically find my store of email addresses. Is there a setting that I have to change. Any help appreciated. Hi, Please make sure that you have automatic name checking enabled by the following. 1. Open Outlook. 2. Select Tools, then Options. 3....

Excel should have the option to merge contents when merging cells.
When Excel merges cells, it keeps only the upper-left-most contents and deletes the others. It would be nice to have the option (maybe a toggle button in the options menu) to concatenate the contents of all the merged cells, so that the information in the lower-right cells is tacked onto the end of the information inthe upper-left-most cell. This is not a big deal, but it would be a nice bell/whistle to save some steps. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I...

Total Invoice revnue
Hi, When i look at a particular account we can c the sales done to that account and also c the individual revenue of the sales. can we got the total amount of sales to that particular account till date. for that shall i need to customize report. or any chance to get it through forms. -- I am new in CRM3.0. trying to study myself. pleas help me ...

Compare cells for identical values
Hi I need to be able to compare a range of cells and highlight if the same value appears to 2 cells in the same row. Does anyone know the VB code that will help me do this? Thanks -- red_debs ------------------------------------------------------------------------ red_debs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14753 View this thread: http://www.excelforum.com/showthread.php?threadid=263746 Hi check out Chip Pearson's website he has lots of stuff on dealing with duplicates http://www.cpearson.com/excel/duplicat.htm Cheers JulieD "re...

Paste Value Macro
Here's a macro that works fine for me. It converts a formula in the cell where the cursor is to a value, or formulas in selected cells to values: Sub ConvertToValue() ' ' Keyboard Shortcut: Ctrl+Shift+D ' Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub What I want is a macro that, after I copy a selection to the clipboard, will allow me to PasteSpecial>ValuesOnly to the new location. I tried: Selection.PasteSpecial Paste:=xlPasteV...