+ Reply to Thread
Results 1 to 1 of 1

Bank Reconciliation - Help

  1. #1
    Registered User
    Join Date
    Rio, Brazil
    MS-Off Ver

    Bank Reconciliation - Help


    I am new to the thread, so if I start breaking any rules, please let me know!
    I am just getting started with VBA, and I feel like the best way to learn is through na applied, hands-on fashion, getting help with the tasks I need to accomplish with Excel.

    I am trying to write a Macros for a simple bank reconciliation process. This involves matching $ values (or R$ in my spreadsheet)corresponding to the same date on the bank side and the ledger side. It also involves many-to-one matching - matching SUM of $ values corresponding to the same date.
    This is easier than the usual bank reconciliation problem because all transactions occur on the same day on both the bank and the ledger side, eliminating the problem of having to match the same transactions across different days, such as when issuing a cheque and having it deposited much later.

    Here's what I'm looking for:
    - First, give me a warning if two different transactions have the same value for a given day. This is simply to warn the user that the transaction description must be checked and matched. Since the bank description is always different than the ledger description even for the same transactions, this must be done manually and doesn´t need code.
    - Then, first match equal dollar values across columns for the same day. If it doesn't find the match, then match sum of all unmatched dollar values across columns for the same day. If that doesn't work, then the problem must be solved manually.
    The spreadsheet attached has it all set up, however it has many days where the cash flows don´t match. This is typical, so don´t worry. The result I´m looking for is for most of the work to be done through code, and the rest manually.

    I wrote all the arguments I want excel to perform:

    •IF R$ values of individual columns X and Y under SAME DATE are Equal -> THEN HIGHLIGHT R$ values yellow -> IF NOT, do nothing. END IF

    •IF R$ values from the SAME DATE, ACROSS column X & Y, are Equal -> THEN MATCH (place letter “x” on columns X´´ & Y´´ for those matches) -> IF NOT, do nothing -> END IF

    •Many to one matching: IF (SUM of R$ value on column X corresponding to leftover blank cells from column X´´ OF SAME DATE - EXACTLY MATCH WITH - SUM of R$ values on column Y corresponding to leftover blank cells from column Y´´ OF SAME DATE) -> THEN place letter “x” on columns X´´& Y´´ for those respective matches -> IF NOT, do nothing.

    •IF leftover blank cells under same day -> THEN highlight values corresponding to blank cells in red.

    Hope this isn't too hard!

    Attached Files Attached Files

+ 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. Bank Reconciliation
    By ravogt91 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-11-2014, 08:22 AM
  2. bank reconciliation.
    By elias jahangir in forum Excel General
    Replies: 3
    Last Post: 05-29-2014, 01:34 PM
  3. Bank Reconciliation
    By Accountant0607 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-24-2014, 10:32 PM
  4. Bank Reconciliation
    By kfoote in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 12-04-2013, 04:45 PM
  5. Bank Reconciliation
    By thameem127 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2012, 03:17 PM


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