+ Reply to Thread
Results 1 to 23 of 23

Bank Reconciliation

  1. #1
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Bank Reconciliation

    I found tons of threads, but none that really answer my question.. I know I got this template on this site, but I want to alter it. Currently the template uses the date to help match, I just want the amounts to match.

    Cheques worksheet-
    How can 18 items be x on one side, and 21 on the other?
    Example- $900 marked off on the bank side, but not in the journal. It all comes down to the date. I need to remove this and just have the items matching by the amount.

    My macro experience is minimal (copying and pasting from similar questions on this site).

    Check out my attachment and let me know what you think. Any help is appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Bank Reconciliation

    kfoote,
    I do not see any code on the attached.
    You also have two sheets. Which sheet?
    Are you comparing column D and H, if so what happens if there are duplicates?

  3. #3
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    Here is the file with the original code..
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    I was just talking about my page "cheques" to start... I'm sure once I figure out one I can get the other!
    I'm trying to compare D & H, X when there is a match.. but right now it is matching dates.. I just want to match up based on numbers.

  5. #5
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Hi kfoote

    Please place the Code in your actual File that exhibits this anomaly and Upload your File with the Code that's causing the issue...perhaps we can debug it for you
    Currently the template uses the date to help match, I just want the amounts to match.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  6. #6
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    Sorry about that! Let me know if this is better!
    Kelly
    Attached Files Attached Files

  7. #7
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Hi Kelly

    I've not played with this Code for some time. As you see in the Attribution, it's not mine. However, over a period of time I've modified the Code and have it in my archives...it seems to work on your File and is in the attached.

    Let me know of issues.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    EXCELHELP.jpg

    I think we are getting closer! Attachment shows that it is adding entries together.

    Kelly

  9. #9
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Hi Kelly

    Pictures are of little value.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

  10. #10
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    Once I opened your file Excel help bank rec UpDate.xls‎ I organized and sorted it, and noticed that the the $10 and $15 Journal Entries were being matched with a $25 bank entry, rather than searching for a "10" and "15".

    I didn't add any additional code.

    Thanks again for your help.
    Attached Files Attached Files

  11. #11
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Hi Kelly

    Yes, this is what the Code does
    Journal Entries were being matched with a $25 bank entry,
    If you wish other Matching Descriptors they'd need to be available in Columns C and I to find available Matches.

    This Code is not currently available and would need to be written. Provide the Matching Descriptors, I'll try to provide the Code.

  12. #12
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    So it can't just match them individually (or exactly)? it pairs them together when the opportunity arises? Thanks for taking the time to explain this to me. I really appreciate it.

    I don't want additional matching descriptors.

    In my perfect world I am looking to have it match up exact duplicates between column D and Column H and mark it with an X in column E and G.

    So if d15 was -22.00 and h5 was -22.00, when you "match" an X would appear in e15 and g5

    Any that weren't exact matches would remain blank and could be manually reconciled.

  13. #13
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Hi Kelly

    Seems to me it would be simpler to manually deal with the exception rather than than the rule.

  14. #14
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    My bank rec is pretty big and it would probably take me longer to find the exceptions than it would to do the whole thing manually. I'm really not sure at this point! I just thought there had to be an easier way.
    Thanks for your help. I appreciate it

  15. #15
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Kelly

    I'm OLD (emphasis on OLD) retired CPA...I've done literally THOUSANDS of Bank Recs in my working life.

    I can make the Code do this if you so choose
    So it can't just match them individually (or exactly)?
    You won't be happy with it. I'll look at it tomorrow.

  16. #16
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    One further point Kelly...what's your Raw Data look like...your Bank Download and your Ledger Account...

  17. #17
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    Tell me why I won't be happy so I can give up on this!
    What is the best option for something like this?

  18. #18
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Hi Kelly

    Seldom will checks that clear the Bank be individual Checks...they're typically a Batch of Checks.

    If we're doing this with the Code none of these Batches will be matched
    So it can't just match them individually (or exactly)?
    If you'll share your Raw Files perhaps I can develop a workaround...

  19. #19
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    My bank statement is organized
    First Bank Card Transaction Type Date Posted Transaction Amount Description

    My statement is organized
    Date Trans Type Detail Amt

    What do you think of this? Attached? Numbering off as they mathc?
    Attached Files Attached Files

  20. #20
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Hi Kelly

    Are you unable to upload the Raw Data?

  21. #21
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Hi Kelly

    If it works for you it works for me.

  22. #22
    Registered User
    Join Date
    10-14-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Bank Reconciliation

    alright I am going to try and work with this one! thank you for your help!!!!!!

  23. #23
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Bank Reconciliation

    Hi Kelly

    Check the Totals of cleared items for each column...make certain they match.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Easy bank reconciliation
    By aboobacker in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-18-2018, 10:42 AM
  2. Bank Reconciliation VBA Help
    By showgun3 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2013, 12:26 AM
  3. bank reconciliation using excel
    By Rajat goel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2013, 06:21 AM
  4. [SOLVED] How would you do a bank reconciliation in excel.
    By Sim in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2013, 10:47 PM
  5. Bank Reconciliation
    By thameem127 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2012, 03:17 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1