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