Bank Reconciliation in Excel

[vikas0577]

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]

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]

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

 

[jardenp]

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.

 

[Joe4]

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).