View Question
 Question
 Subject: Bank reconciliations using MS Excel Category: Business and Money > Accounting Asked by: ricky1240-ga List Price: \$50.00 Posted: 09 Jan 2003 05:28 PST Expires: 08 Feb 2003 05:28 PST Question ID: 139777
 ```I wish to do a bank reconciliation using Excel 2000. I will have a column of cash book entries and a column of bank statement entries. I need a formula or macro/s to compare the two columns. Where the entries match both entries (inc description) would then be removed to another worksheet leaving only unreconciled items (all vacant rows to be removed)```
 Subject: Re: Bank reconciliations using MS Excel Answered By: jbf777-ga on 10 Jan 2003 11:42 PST
 ```IMPORTANT -- PLEASE READ This answer is not finished until you're satisfied with it. If you choose to rate this answer, please only do so AFTER allowing me the opportunity to make it satisfactory to you. Thank you for your understanding. =============================================================================== Greetings - This is actually much more easily handled in Microsoft Access, but if I were to handle this in Excel, the following is how I would do it: The way I read your setup, you've got 4 columns: your cash book entries, your bank statement entries, a cash book description column, and a bank statement description column. You need to make a fifth column. Label it RECONCILED. When we're done making the formula, each cell in this column will denote whether or not the contents of your cash book entries and bank statement entries match. So, here's an example: Column A Column B Column C Column D Column E 1 CASHBOOK BANKSTATEMENT CASHDESCRIPT BANKDESCRIPT RECONCILED 2 \$400 \$400 Bill Payment Bill Payment 3 \$800 \$293 Bill Payment Bonus Payout 4 \$200 \$200 Supplies Lunch As we can see, the cells in row 2 match. The third and fourth rows don't. We need to create a formula [also called "function"] in the first row of the RECONCILED column [which will go in each cell of that column] that will compare the cells of columns A and B and columns C and D [on each row] and determine if they match. If they match, a TRUE will be entered in the cell. If they don't match, FALSE will be entered. Please note, the description entries for your cash book and your bank entries MUST have the same naming scheme in order for this to work. For instance, if your cash book description says "Bill Payment" for when you've paid a bill, but your bank statement descriptions says "Payment of Bill", then even though conceptually these two things are the same, the formula will not be able to match them. In the first cell of your RECONCILED column, you want to enter the following formula: =AND(A2=B2,C2=D2) This "AND" formula/function works by evaluating up to 30 expressions, separated by commas [in the above example, we have 2 expressions being evaluated]. Each expression must be true in order for it to return [or, "render a calculation of"] TRUE. If one of the expressions is false, this function will return FALSE. Once you have that formula entered into that first cell, all you have to do is "auto-fill" this formula into the rest of the cells in that column by clicking and holding the bottom right corner of that cell, and dragging it down into the rest of your cells. This will automatically fill the formula in for every row and do the associated calculations for each row. Now the first 4 rows of your spreadsheet should look something like this: Column A Column B Column C Column D Column E 1 CASHBOOK BANKSTATEMENT CASHDESCRIPT BANKDESCRIPT RECONCILED 2 \$400 \$400 Bill Payment Bill Payment TRUE 3 \$800 \$293 Bill Payment Bonus Payout FALSE 4 \$200 \$200 Supplies Lunch FALSE . . . All the way down your spreadsheet, the RECONCILED column should be filled with TRUE or FALSE as appropriate [manually double-check a few to make sure it's calculating correctly. Now, all you have to do is highlight your entire spreadsheet and sort it by your RECONCILED column [Column E in this example]. All of your reconciled bank entries will be listed consecutively, and all of your unreconciled entries will also be listed consecutively. You can simply copy and paste any entries you wish into another worksheet. I hope this was clear. If you have any questions, please don't hesitate to ask. Additional Link: A few Useful Microsoft Excel Resources http://www.possibility.com/Lgc/Excel.html Excel Tutorials by John F. Lacher CPA http://lacher.com/toc.htm Search strategy: See excel help file for more information on this and other functions jbf777-ga GA Researcher``` Clarification of Answer by jbf777-ga on 10 Jan 2003 11:48 PST ```Oops... the system misformatted my answer. The "RECONCILED" should be under the Column E heading. Let my try it again: IMPORTANT -- PLEASE READ This answer is not finished until you're satisfied with it. If you choose to rate this answer, please only do so AFTER allowing me the opportunity to make it satisfactory to you. Thank you for your understanding. =============================================================================== Greetings - This is actually much more easily handled in Microsoft Access, but if I were to handle this in Excel, the following is how I would do it: The way I read your setup, you've got 4 columns: your cash book entries, your bank statement entries, a cash book description column, and a bank statement description column. You need to make a fifth column. Label it RECONCILED. When we're done making the formula, each cell in this column will denote whether or not the contents of your cash book entries and bank statement entries match. So, here's an example: Column A Column B Column C Column D Column E 1 CASHBOOK BANKSTATEMENT CASHDESCRIPT BANKDESCRIPT RECONCILED 2 \$400 \$400 Bill Payment Bill Payment 3 \$800 \$293 Bill Payment Bonus Payout 4 \$200 \$200 Supplies Lunch As we can see, the cells in row 2 match. The third and fourth rows don't. We need to create a formula [also called "function"] in the first row of the RECONCILED column [which will go in each cell of that column] that will compare the cells of columns A and B and columns C and D [on each row] and determine if they match. If they match, a TRUE will be entered in the cell. If they don't match, FALSE will be entered. Please note, the description entries for your cash book and your bank entries MUST have the same naming scheme in order for this to work. For instance, if your cash book description says "Bill Payment" for when you've paid a bill, but your bank statement descriptions says "Payment of Bill", then even though conceptually these two things are the same, the formula will not be able to match them. In the first cell of your RECONCILED column, you want to enter the following formula: =AND(A2=B2,C2=D2) This "AND" formula/function works by evaluating up to 30 expressions, separated by commas [in the above example, we have 2 expressions being evaluated]. Each expression must be true in order for it to return [or, "render a calculation of"] TRUE. If one of the expressions is false, this function will return FALSE. Once you have that formula entered into that first cell, all you have to do is "auto-fill" this formula into the rest of the cells in that column by clicking and holding the bottom right corner of that cell, and dragging it down into the rest of your cells. This will automatically fill the formula in for every row and do the associated calculations for each row. Now the first 4 rows of your spreadsheet should look something like this: Column A Column B Column C Column D Column E 1 CASHBOOK BANKSTATEMENT CASHDESCRIPT BANKDESCRIPT RECONCILED 2 \$400 \$400 Bill Payment Bill Payment TRUE 3 \$800 \$293 Bill Payment Bonus Payout FALSE 4 \$200 \$200 Supplies Lunch FALSE . . . All the way down your spreadsheet, the RECONCILED column should be filled with TRUE or FALSE as appropriate [manually double-check a few to make sure it's calculating correctly. Now, all you have to do is highlight your entire spreadsheet and sort it by your RECONCILED column [Column E in this example]. All of your reconciled bank entries will be listed consecutively, and all of your unreconciled entries will also be listed consecutively. You can simply copy and paste any entries you wish into another worksheet. I hope this was clear. If you have any questions, please don't hesitate to ask. Additional Link: A few Useful Microsoft Excel Resources http://www.possibility.com/Lgc/Excel.html Excel Tutorials by John F. Lacher CPA http://lacher.com/toc.htm Search strategy: See excel help file for more information on this and other functions jbf777-ga GA Researcher``` Clarification of Answer by jbf777-ga on 10 Jan 2003 11:49 PST ```Well, this time it did the second spreadsheet example right... so you know what the first should look like.``` Request for Answer Clarification by ricky1240-ga on 11 Jan 2003 00:23 PST ```Thanks Solution is good so far and will be a help, however it may require a further preliminary step; What can I do when, (as is the case) the list of figures are not sitting side by side, eg. my present problem has circa 8000 entries in one column and 8000 in another column. the first column is the "paid or lodged" date and the second is the "bank entry" date. These entries therefore will not be presented in a side by side fashion to allow the "function matching" solution you proposed. Cheers``` Clarification of Answer by jbf777-ga on 11 Jan 2003 12:50 PST ```Hello - If I understand you right, you're saying your columns are not next to each other? For example, you have one column as column B and one column as column F? Columns don't have to be side by side in order for any formula to work. For instance in the following formula: =AND(A5=V4, B2=R12, M7=X7) Cell A5 is compared to V4 [column A vs. column V], cell B2 is compared to R12 [column B vs. column R], and M7 is compared to X7 [column M vs. column X]. If A5=V4 AND B2=R12 AND M7=X7 the function will be evaluated as True. You can simply adjust the formula as need-be to reflect how your worksheet is set up. Please let me know if you need any additional clarification. I'm here to help. Thanks, jbf777-ga GA Researcher```
 ```Hi, Well, the solution which will solve your probem is a simple Excel vba macro written to compare values in all the rows of different column , and if a matching value is found move them to next sheet and cleanup the entries in the main sheet. If you wish, post the sample format of your excel sheet data. I can help by writing a macro for you. Thanks, RAC```
 ```Thanks RAC I think you understand my prob. I have set out a short example of my spreadsheet Nominal ledger Bank Account Bank Statement A B C D E F G H I 1/1/03 chq.no. 15455 Wages -£300 1/1/03 Bank Giro 333 £1,250 1/1/03 Bank Giro 332 Receipt £900 2/1/03 Chq no. 15454 -£50 2/1/03 chq.no. 15456 Fuel -£100 3/1/03 chq no. 15455 -£300 3/1/03 Bank Giro 334 Receipt £559 3/1/03 chq no. 15456 -£100 4/1/03 chq no. 15457 Expns -£25 4/1/03 Bank Giro 332 £900 The actual speadsheet extends to just short of 15,000 lines! ((As a temporary solution I have allocated a index column to the two sections above. I have then amalgamated the two lists vertically (ie. col I & E combine) and sorted by a) value, b)ref no. This creates matching pairs one after the other vertically. I can then enter a "Y" in the next column to identify matched items, which can then be sorted by "y"s and cut & pasted elswhere.Although this works it is clearly a fair bit of work involved and I can visualize a little Macro would churn through this very simply and quickly.)) I would appreciate if could send me sample macro Thanks```
 ```Hi, Thanks for the dataformat and explaining the way you are working now. Will write a macro in a day and let you know. Thanks, RAC```
 ```Hi, Attached in the following link sample xls file with macro. http://rac-ga.tripod.com/input.xls Get the input.xls. The file has 3 sheets. "Inputbefore" Sheet shows how the sheet with data before processing will looklike. "Output" Sheet shows the processed sheet and "Input After" Sheet shows the sheet after processing run. How to run the program: Keep the data to be processed in a excel sheet as in the format of InputBefore sheet. First line is column heading and data starts from 2nd line. 1.Open the input.xls 2.Open your data xls file to be processed. 3.In the data xls file from tools menu-->Macro-->macros 4.Select Input!Reconcile macro. 5.Press the run button. 6.Wait until processing Over msgbox is displayed. It may take many minutes based on the volume of data. The macro will create a new sheet "Output" and add the processed data in that sheet. Hope this will solve your problem. Please let me know if you need any further help. Thanks, RAC```