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