# Reconciliation

1. ## Reconciliation

Dear , I am attaching herewith an excel file. I have used here an if formula but i am not getting the equal matched amount from both file, Please see the attached file and help me...RECO.xlsx  Register To Reply

2. ## Re: Reconciliation

The problem occurs because you have some values which occur twice in one sheet and once on another.

For instance in BRJ you have two rows with
26-11-2013 -2 000 000,000
and in
BSR just one such case. Thus in BRJ your formula in column C returns in both rows OK (indeed in second sheet this dataset is only once).
While in BSR you got No Match (well - not exactly "no match" but rather "no EXACTLY ONE match" - there are two.

So I'd suggest slightly modified formula:
=IF(SUMPRODUCT((A5=BRJ!A\$5:A\$1300)*(B5=BRJ!B\$5:B\$1300))=SUMPRODUCT((A5=A\$5:A\$1300)*(B5=B\$5:B\$1300)),"Ok","No Match")

and similar for second sheet
=IF(SUMPRODUCT((A5=BSR!A\$5:A\$1300)*(B5=BSR!B\$5:B\$1300))=SUMPRODUCT((A5=A\$5:A\$1300)*(B5=B\$5:B\$1300)),"Ok","No Match")  Register To Reply

3. ## Re: Reconciliation

You have on BSR 87 many duplicate matches for data on BRJ than means matches that match more than 1 time.  Register To Reply