How to reconcile (tally items) in two sheets of an excel workbook.

I do accounts reconciliation , matching ledger and passbook items ... Does
anyone know how it could be done efficiently in excel rather than manually
ticking the items in both sheets .

Hi.
One of our monthly reconciliations involves tracking all
cleared and outstanding checks, where we process thousands
at a time, and the process sounds like it may be similar
to your situation. We download a detailed list of all
checks (incl check number and amount) from our GL and
paste that in a worksheet with a range name Chart1 and
sort that by check number.
Then in another range we call Chart2 we download the
cleared check number and amount from the bank statement
where is is sorted.
Then we insert the following formula into a helper column
=IF(ISERR(VLOOKUP(E1,CHART2,2)),0,VLOOKUP(E1,CHART 2,2))
which looks up the check number and returns the associated
$ amount for which the check cleared.
We then have a formula that calculates any variance
between what we posted the check for and what the bank
cleared it for, as they will not always be the same.
One additional check that we use is a match function that
we insert beside chart2 to ensure that there are no checks
clearing the bank, that are not listed either on our
outstanding checks log, or on the checks we posted in the
current month (both included in Chart1).

You can tell at a glance which items are out of balance,
or have not yet cleared. Bank rec now takes a matter of
seconds instead of days.

HTH

-----Original Message-----
I do accounts reconciliation , matching ledger and
passbook items ... Does
anyone know how it could be done efficiently in excel
rather than manually
ticking the items in both sheets .

.

Dear Sir,

What you said about the reconciliation, mine is exactly the same . But
unfortuantely I couldn't do wat you have explained. I will be very greatful
if you could send an excel file with dummy figures and explaining the steps.
Coz even after several tries I'm not able to accomplish the said method.

Kindly elaborate on it . and if possible send me a detailed msg on
, probably with an excel file.

Kind Regards

Md Ali

"mzehr" wrote:

Hi.
One of our monthly reconciliations involves tracking all
cleared and outstanding checks, where we process thousands
at a time, and the process sounds like it may be similar
to your situation. We download a detailed list of all
checks (incl check number and amount) from our GL and
paste that in a worksheet with a range name Chart1 and
sort that by check number.
Then in another range we call Chart2 we download the
cleared check number and amount from the bank statement
where is is sorted.
Then we insert the following formula into a helper column
=IF(ISERR(VLOOKUP(E1,CHART2,2)),0,VLOOKUP(E1,CHART 2,2))
which looks up the check number and returns the associated
$ amount for which the check cleared.
We then have a formula that calculates any variance
between what we posted the check for and what the bank
cleared it for, as they will not always be the same.
One additional check that we use is a match function that
we insert beside chart2 to ensure that there are no checks
clearing the bank, that are not listed either on our
outstanding checks log, or on the checks we posted in the
current month (both included in Chart1).

You can tell at a glance which items are out of balance,
or have not yet cleared. Bank rec now takes a matter of
seconds instead of days.

HTH

-----Original Message-----
I do accounts reconciliation , matching ledger and
passbook items ... Does
anyone know how it could be done efficiently in excel
rather than manually
ticking the items in both sheets .

.