Bank Reconciliation in Excel

vikas0577

New Member
Joined
Jul 1, 2010
Messages
17
Dear All,
I require to reconcile 2 sheets every month and its very tedious.
Can this be automated by a macro. here is what I need to do.
sheet 1 is the bank statement , sheet 2 is the reconciliation sheet, where I have to search the items from the sheet 1 and if the item is present in sheet 2 then I have to enter the date from sheet 1 to sheet 2, thus clearing all the similar items in both the sheets.
Pl. help thanks
 

jardenp

Active Member
Joined
May 12, 2009
Messages
342
I'm not sure what you mean by "clearing all the similar items," but a VLOOKUP formula is what you need to check the contents of Sheet2 against Sheet1 and return a date. It can be automated by macro. You'll need to provide details of how the columns are arranged for specific help though.
 

vikas0577

New Member
Joined
Jul 1, 2010
Messages
17
Dear jardenp,
Thanks for prompt reply, by "clearing all the similar items," I mean that the similar records are marked off in the reconciliation sheet, thus affecting the Balance in the sheet, which is to be matched with the bank statement.
Any ways I am providing you with both the sheets format as under.
This is Bank Statement sheet 1

image removed

This is the Reconciliation sheet 2

image removed

Now here if you see when I enter the dates for the similar records ( which is found by the cheque numbers in both the sheets) the balance amt. in yellow changes and after all the records ( from the bank statement ) are marked by the dates in the cleared on column the yellow balance matches with the closing bal. in the bank statement sheet.

I hope you will now understand this case.
Also if you can send me your email id then I can attach the files so that you get a better idea, of what I exactly mean.
Thanks
Vikas
 
Last edited by a moderator:

jardenp

Active Member
Joined
May 12, 2009
Messages
342
So what do you want to happen? Do you want the Receipt column in sheet 2 to look up the check number in sheet 1 and return the "TXN Amount(INR)" value from that row? You could then set up the "Cleared On" column to fill when the Receipt and Payment columns match (maybe fill with "Cleared", which could then be changed to the clear date).

Also, I wouldn't send bank statement spreadsheets to strangers on the Internet, no matter how nice and helpful they are. :) And you should edit out the account numbers from your post!

So, tell me what you specifically want to do and I'll see if I can help.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
vikas,

I have removed the images showing the financial information.

Please don't ever post real personal, financial, or confidential data on these public forums! At best, you could upset your clients, at worst, you could be violating company policies or local laws (and get yourself and/or your company in a heap of legal trouble).
 

Forum statistics

Threads
1,082,641
Messages
5,366,713
Members
400,915
Latest member
NFlem

Some videos you may like

This Week's Hot Topics

Top