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. Follow

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
      excel/office,check under tools>add-ins)
      put the data set in let's say A2:A8, in B2:B8 put a set of ones {1,1,1 etc}
      in the adjacent cells
      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
              dco.Add Key:=y, Item:=1

              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
                    dcn.Add Key:=y & s, Item:=u
                    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
            Set r = Worksheets.Add.Range("A1")
            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)
        'ad hoc quicksort subroutine
        '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.
            Press ALT+F11 again to return to your Excel worksheet.
            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



<JLKaye@gmail.com> wrote in message news:1155786498.667923.325810@h48g2000cwc.googlegroups.com...
| 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
Reply:

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: ...

Paste to adjacent cells
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...