Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    need help to balance a long spreadsheet

    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.
    Attached Files Attached Files

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    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.

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    continue with more clear inquiry

    Quote Originally Posted by dguillett@gmail.com View Post
    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.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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

  5. #5
    New Lounger
    Join Date
    Sep 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    YangYangLi,

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

    Test File: Answer 9991 account balance the book.xlsx

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

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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

    pivot table.png
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Sep 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thank you. The pivot table is helpful.

  9. #9
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    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.

  10. #10
    New Lounger
    Join Date
    Sep 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dguillett@gmail.com View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •