+ Reply to Thread
Results 1 to 2 of 2

Matching Bank Statements

  1. #1
    Registered User
    Join Date
    01-18-2005
    Posts
    1

    Matching Bank Statements

    I am trying to find an easier way to match a bank statement with a general ledger detail report in order to make sure that the checks balance and clear. Basically I could put information on 1 sheet like the following:

    Column A and B are from bank Statement
    Col A = Check Number
    Col B = Check Amount

    Column C and D are from General ledger
    Col C = Check Number
    Col D = Check Amount

    I would like to make it so that they would match side by side and if they did not it would leave a blank space next to it. I am a little familiar with ecel but this formula is a little above my head. Any help would be appreciated.

    Thanks

  2. #2
    Forum Contributor
    Join Date
    09-05-2004
    Location
    Melbourne
    Posts
    193
    This is the basis for how I conduct reconciliations in Excel. It may be helpful for what you are trying to achieve.

    Col A - Bank or GL
    Col B - Check Number
    Col C - Amount
    Col D - Formula as follows =ABS(C1) for cell D1 etc
    Col E - Matched? (place 'Y' for matching items)
    Col F - Formula as follows =IF(E1="Y",D1,"")

    I also have a cell summing all of column F.

    I then sort by Col D which will get all the values sorted in order (or by Col B to get all check numbers in order) and start matching them off by placing 'Y' next to matching items. The cell summing Col F will ensure that the amounts match correctly. That's the bare bones of it but there is room from adjusting to your needs and the addition of bells & whistles.

    HTH

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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