• need help to balance a long spreadsheet

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » need help to balance a long spreadsheet

    Author
    Topic
    #496488

    On the spreadsheet, I need to march the debit and credit throughout the month. I am now manually to compare each line data. The debit and credit may happen in different days. Also, I want to create a macro to split the debit and credit into two columns, also have a running balance for each day. Please see attached file.

    Thank you for your help.

    Viewing 6 reply threads
    Author
    Replies
    • #1468028

      You should always provide an example of what you want
      By which column(s) do you want to match for the debit/credit
      Several ways to do this.

      • #1468062

        You should always provide an example of what you want
        By which column(s) do you want to match for the debit/credit
        Several ways to do this.

        Sorry, this is my first post. I was not clear. I want column I to match +/-.
        You can find the reference information on column H. E.g. line 8&9, the same reference is 03603, so I know these two transactions offset each other. But not Ll lines are close to each other, I have to scroll down the entire page to search. I wonder if there is easy way to match up two numbers.
        Many thanks.

    • #1468086

      Dear Yangyang

      Welcome to the lounge.

      Everyone want to help and I know excel can do this, but the data provided is very difficult to understand. What is “Summarization Record”? These entires appear to cancel out other records. See amounts recorded on 9/15 for minus 249.71 and minus 513.00 and the summarization record on 9/17 of positive 762.71 they total to zero but have no common reference in Col H or any other columns. In other words without a common reference Excel could never match the 3 amounts.

      Can better explain what needs to be matched?

      Regards,

      TomD

    • #1468089

      Hi Tom, thanks for the response. Column H is the column I use to find common reference. Summarization record is the amount that could balance several line items. I don’t expect all lines are matching up, but just want to have a macro which does the most matching. thank you.

    • #1468094

      YangYangLi,

      Here’s a possible approach.
      1. Setup an Advanced Filter to select Unique Vendor References. Use Range Names for Database, Criteria, & Extract
      37933-AdvFilter
      2. Create Dynamic RangeNames for Vendor Reference and Amount columns (one each)
      3. Use a SumIf to total the transactions by Vendor Reference.
      37934-AdvFilterSumIf

      Test File: 37932-Answer-9991-account-balance-the-book

      Look at the Defined Range Names on the Formula Tab to see how it’s done.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1468095

      yang,

      You can do this easily with a pivot table. See the revised spreadsheet. I split your debits with the formula, =IF(I2>0,I2,0) and your credits with the formula =IF(I2<0,I2,0) in columns J and K respectively. Then copy down. Currently, your credits and debits are off by $60,000.00+ and you need to complete your postings. When updated, you should be all set.

      HTH,
      Maud

      37936-pivot-table

    • #1468119

      thank you. The pivot table is helpful.

    • #1468259

      When you say you need to match what do you do when you match. Do you keep the data or if it cancels out to 0, delete all pertinent entries.
      row 2 -34.56
      row 22 +10
      row 42 +24.56
      or ??

      Daily total should take into account these deletions and adjust accordingly.

      • #1468379

        When you say you need to match what do you do when you match. Do you keep the data or if it cancels out to 0, delete all pertinent entries.
        row 2 -34.56
        row 22 +10
        row 42 +24.56
        or ??

        Daily total should take into account these deletions and adjust accordingly.

        I still need to keep all the data that match up. I found some other help from another site. but all are helpful. I am learning a lot more through you guys. thank you very much for your follow up.

    Viewing 6 reply threads
    Reply To: need help to balance a long spreadsheet

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: