Bank Reconciliation

[Guest]

I want to reconcile two lists, list(1) is payments shown on bank statement
and list(2) is payments shown on ledger, I want to produce list(3) which is
payments shown on bank statemnt BUT not shown on ledger and list(4) which is
payments shown on ledger BUT not on bank statement.
I tried a simple formulae =IF(A1=(B1:B6),"",A1) for to produce list(3) but
it didn't work because I might have four lines with the same amount ($100)
in list(1) and three lines with the same ($100) in list(2) and my formulae
fails in picking up the unreconciled amount.

Thanks in advance

 

[Roger Govier]

Hi Tarig

Assuming your Bank statement is on Sheet1 and contains Cheque No in
column A and Amount in Column B.
Assuming your Ledger is on Sheet2 with the same layout.

On sheet2 in C2 enter
=IF(COUNTIF(Sheet1!$A:$A,A2),A2,"")
On sheet2 in D2 enter
=IF(C2="","",SUMIF(Sheet1!$B:$B,"="&C2))
On sheet2 in E2 enter
=IF(B2=C2,"Reconciled","Not Reconciled")
Copy down as required

Column C will show whether the same Cheque number appears on both
sheets.
Column E will test to ensure that the amounts are the same for the same
cheque number.

 

[bobocat]

supposed you have one workbook with 2 sheets
sheet1, named "bank", at column A, record amount of the bank statement
list1 = sheet 1 column A

sheet2, named "ap", at column B, record the amount of ledger
list 2 = sheet 2 column B

now in Sheet 1, column C, enter the following function to check "payment not
show in ledger"
at c2:
=if(countif(ap!B:B,A2)=0,A2,IF(COUNTIF(A$1:A2,A2)<=COUNTIF(AP!B:B,A2),"",A2))
copy downward

in sheet "AP" column, enter the following function to check "payment not
show in statement:
at d2: =IF(COUNTIF(BANK!A:A,B2)=0, B2,
IF(COUNTIF(B$1,B2)<=COUNTIF(BANK!A:A,B2),"",B2))
copy downward

 

[Bobocat]

Hi Tarig,

In order to reconcile "ap", we need the second the formula,
for the following example,
Assuming that Column A is Bank, Column C is "AP"
Column E is List 3 payments shown on bank statemnt BUT not shown on ledger
Column F is List 4 payments shown on ledger BUT not on bank statement
Then at E2,
=IF(COUNTIF(C:C,A2)=0,A2,IF(COUNTIF(A$2:A2,A2)<=COUNTIF(C:C,A2),"",A2))
At F2:
=IF(COUNTIF(A:A,C2)=0,C2,IF(COUNTIF(C$2:C2,C2)<=COUNTIF(A:A,C2),"",C2))

Bobocat

 

[Guest]

Thank you, this is exactly what I am looking for