Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 33

Thread: Clearing Account formula ?

  1. #1
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016

    Clearing Account formula ?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Trying to figure out a formula to help reconcile clearing account with over 5K lines of data each month.
    Currently it is a manual process of using ABS formula and colour coding entries that offset, example $100 with -$100 = Zero.
    It takes about 4 hours to be this each month and was thinking there must be a better way.

    Any ideas?

  2. #2
    Admin Alumnus Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,755
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please post a sample sheet ( no pics please). Thx
    Thank you Ken for this secure forum.

  3. #3
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016

    Sample Clearing account data

    Hi,

    Here is attached excel file sample data I used for May clearing, I tried a few formula's but without success.
    Let me know if you have any ideas or want to discuss process in more detail.

    Thanks

    Andrew

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,726
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    Nothing attached ...
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016
    I wasn't sure if attached or not. Is there a trick to it?

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,726
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    Making sure you upload, save and submit will help.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016

    Sample Clearing account data

    File attached
    Attached Files Attached Files

  8. #8
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016
    Hello, Still trying to find a way to automate clearing account. Anyone have any good ideas, would use of Power Query help?

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,062
    Articles
    0
    Excel Version
    365
    See table at Column M of the attached and tell me if you can do anything with it?
    See columns X & Y
    Assumes amounts and vendor name should match.

  10. #10
    Acolyte Coach Andrew's Avatar
    Join Date
    Jun 2021
    Location
    Ottawa
    Posts
    20
    Articles
    0
    Excel Version
    Excel 2016
    Hello, I see the results if Y is blank then not cleared. The others have possibilities, not sure understand what the PQ is doing. There also appears to be errors in it. Can you explain further?

Page 1 of 4 1 2 3 ... LastLast

Posting Permissions

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