• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding Reconciliation items In Bank Statment & GL for Bank Reconcilation.

K

Kabeer

New Member
Dear Group members,

I have recently joined this community to improve my excel knowledge and efficiency in work. I am posting my query to seek your help and expert advise. I am explaining my query as....

How to identify reconciliating items between two set of data as in Bank Statement and General Ledger to prepare Bank reconciliation . The data involved is humongous and there are no common unique values to identify as in the unique cheque numbers or reference numbers. Please guide how I can identify the reconciliation items as in " Those entries ( amounts) which are in Bank and not in GL and those(amounts) which are in GL and not in Bank. I am not able to use Vlookups as the amounts can be duplicating or same in value and there are no other unique values to give references.

I am attaching an excerpt from the data. Please advise what formulas/techniques to use.

I would greatly appreciate your guidance and would be grateful for your support.

Regards...
 

Attachments

  • Excel Help.xlsx
    68.8 KB · Views: 10
Hi Kabeer ,

Are you looking to reconcile exact amounts on a one-to-one basis , or can there be a one-to-many or many-to-one relationship between amounts in the two categories ?

If it is one-to-one , then it will be possible , but if it is one-to-many or many-to-one , then I doubt that anything can be done , unless you use specialized software ; even then , given that your data is huge , it may be a time-consuming task.

Narayan
 
Mr. Narayan ,

Thanks for your response. I am not able to understand your comment thoroughly but I would explain the relationship between the Data. My objective is to identify those values which are existent in one record and missing in other. Like the enty in Bank Statment and missing in GL or enty in GL and missing in Bank Statment. I know its a hard task as no other unique values/references are provided.

I reckon it is One to One relation between the entries as it would be practically impossible to identify club entries without any common reference.

Please guide.
 
Hi ,

There are 4 columns of data viz. Bank Debit , Bank Credit , GL Debit and GL Credit.

Suppose we take the first amount of 257.40 ; where are we supposed to match this amount ?

Similarly for say the following amounts :

The first amount in GL Debit , of 2700

The first amount in Bank Credit , of 9000 ( in row 39 )

The first amount in GL Credit , of 405084

If you can fully explain how we would go about matching these amounts , it might help.

Narayan
 
Dear Narayan,


As for all Bank Recon. Bank Debits are to be searched in GL Credits and Bank Credits to be searched in GL Debit and vice versa on GL Side.

Bank Debit of 257.40 is to be searched in GL Credit whilst 9000 Bank Credit to be searched in GL Debit.

2700 in Bank Credit and 405,084 in Bank Debit.

Vlookup doesn't help as there is duplication of amounts such as in E82 and E83.

Thanks
 
Hi:

As per my understanding this can be done only with additional information and can have only one-way comparison, say reconcile GL to Bank statement. But, you should bring in additional info like transaction Id or something which can be used for reconciliation. Simply giving a bunch of numbers won't make any sense. I would suggest you to fix your process first before going for any form of solution.

Thanks
 
Hi Kabeer ,

Two points :

1. If you try more frequently asking for a solution , it does not necessarily follow that you will get a solution faster ; all members of this forum are volunteers , who have to take time away from their other commitments to devote to your problem ; this might or might not be possible , but it is in no way dependent on your asking. Even without asking a second time , many people get their questions answered by those who are inclined to help.

2. When a problem has not been answered immediately , there can be many reasons , one of which is that it is a difficult one ; if you have come to a forum , any forum , as a last resort , when it is already late for you , it does not necessarily follow that others will now double up to give you help at the earliest. Others may or may not view your problem with the importance and urgency that you think it deserves.

In the light of the above , if you are prepared to wait , a solution can be found , but if you are impatient , that itself is enough to put people off solving your problem , since if I am able to find a solution after a day or two , after spending some time , if you are no longer expecting a solution , then the time I have spent trying to arrive at a solution is wasted.

Please confirm that you are interested in a solution to your problem , and that you are prepared to wait for a day or two to get it.

If not , I for one , will move on to trying to solve other problems.

Narayan
 
Dear Narayan,



There is no problem in waiting for days or so ; the fact is that I was not aware of the norms on this forum and I just tried to re-post my query . I was not aware that you already took a note of it and you are already working on it. I kind of apologize for posting it again but to be honest I did not know that it is actually unethical or inappropriate to follow my query.

Apologies and Great thanks for your work . I would wait for the solution .
 
Dear Nebu,

I have already suggested to improve the business process. There are issues at entry levels which are causing this hectic exercise for me, where I have to do matching manually i.e Line by Line. The decision makers are not ready for now to make any amendments in the process for unknown reasons.

Regards.
 
Hi Kabeer ,

No issues ; it is neither unethical nor inappropriate to follow up on your problem after you have posted it ; my point was that it may or may not bring any dividends.

See the uploaded file. It is just a start , and we need to work much more before you get what you want.

I think you need to split up your problem into 4 sub-problems viz.

Matching of Bank Debit with GL Credit

Matching of Bank Credit with GL Debit

Matching of GL Credit with Bank Debit

Matching of GL Debit with Bank Credit

Narayan
 

Attachments

  • Excel Help.xlsx
    134.8 KB · Views: 29
Hello Mr. Narayan,

To Start with I would thank you again for taking out time for my problem. To be honest with you with my little knowledge of Excel I am not able to understand your solution and the breakdown of the problem to sub-problems that you have suggested.

Thanks and Looking for your further advise. Please guide.
 
Hi Kabeer ,

What I meant was that what ever I have done in my uploaded file is for matching the entries in the Bank Debit column ( column B ) with those in the GL Credit column ( column F ).

Even this is not complete ; what has been done is to segregate the entries into 3 categories :

Matched - where there is a match for an entry in the Bank Debit column with an entry in the GL Credit column

No Match - where a match has not been found

To further check - where there are multiple entries in the Bank Debit column , which may or may not have matches in the GL Credit column

Those in the first two categories can be removed from the next step of checking which can concentrate only on the third category.

This same exercise will need to be repeated for the other 3 comparisons :

matching the entries in the GL Credit column ( column F ) with those in the Bank Debit column ( column B ).

matching the entries in the Bank Credit column ( column C ) with those in the GL Debit column ( column E ).

matching the entries in the GL Debit column ( column E ) with those in the Bank Credit column ( column C ).

All this is going to take time.

I have just seen that my formulae are not returning the correct results ; I'll upload the file after I have revised them.

Narayan
 
Hello Mr. Narayan,

I am greatly thankful for your time and effort and would look forward for the solution of the query.

Thanks a million.
 
You must log in or register to reply here.
Back
Top