Bank Reconciliation
 

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

--
tarig elsawi

Bank Reconciliation
 

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

Roger Govier


"Tarig" wrote in message
...

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

--
tarig elsawi

Bank Reconciliation
 

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


"Tarig" ...

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

--
tarig elsawi

Bank Reconciliation
 

Hi bobocat, I have tested your formula with the following data:
"BANK" "ap"
10 10
12 12
15 15
20 15
20
The formula has not identify the second 15 on "ap" as an unreconciled amount
--
tarig

"bobocat" wrote:

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


"Tarig" ...

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

--
tarig elsawi

Bank Reconciliation
 

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)<=CO UNTIF(C:C,A2),"",A2))
At F2:
=IF(COUNTIF(A:A,C2)=0,C2,IF(COUNTIF(C$2:C2,C2)<=CO UNTIF(A:A,C2),"",C2))

Bobocat

"Tarig" wrote in message
...

Hi bobocat, I have tested your formula with the following data:
"BANK" "ap"
10 10
12 12
15 15
20 15
20
The formula has not identify the second 15 on "ap" as an unreconciled
amount
--
tarig

"bobocat" wrote:

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


"Tarig" ...


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

--
tarig elsawi

Bank Reconciliation
 

Thank you, this is exactly what I am looking for
--
tarig

"Bobocat" wrote:

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)<=CO UNTIF(C:C,A2),"",A2))
At F2:
=IF(COUNTIF(A:A,C2)=0,C2,IF(COUNTIF(C$2:C2,C2)<=CO UNTIF(A:A,C2),"",C2))

Bobocat

"Tarig" wrote in message
...

Hi bobocat, I have tested your formula with the following data:
"BANK" "ap"
10 10
12 12
15 15
20 15
20
The formula has not identify the second 15 on "ap" as an unreconciled
amount
--
tarig

"bobocat" wrote:

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


"Tarig" ...


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

--
tarig elsawi