BANK RECONCILIATION ????

[Guest]

Dear All,

I'm trying to find a way to reconcile (tally) items on two sheets .

1.One sheet has the Ledger items .
2. Second sheet has Bank entries .
*Both sheets have one reference no. that is similar and most of the time
(90%) amount is also same.

I have to check them all (more than thousand items) by printing and ticking
each item for differences in amounts.

Please advise a way that this can be done efficiently either in Excel or
access.

Regards,
Md Ali (muzainiali @ yahoo.com)

 

[Frank Kabel]

Hi
though I wouldn't do this in either Access or Excel if this is for a
serious banking business you may try the following in Excel:
1. Assumptions:
- sheet1 contains the ledger data with column A = Unique reference and
column B = amount
- sheet 2 contains the bank entries with column A = Unique reference
and column B = amount
- The reference IDs have to be IDENTICAL and UNIQUE

2. Now try the following formula on sheet 2 in column C (cell C1):
=IF(VLOOKUP(A1,'sheet1'!$A$1:$B$2000,2,0)<>B1,"Different amounts","")
and copy this down for all rows