Reconcilation of accounts

[Shabbo]

Dear Sir,
I wanted to do reconciliation of accounts as on the basis of "date,vehicle number and amount" but my data is not simple to reconcile.

Can you please advise.

 

[Chihiro]

Not sure what you need. But try something like below in D4 of "Statement of accounts" and copy down.
=INDEX('as per ledger'!$B$2:$B$339,SUMPRODUCT((A4='as per ledger'!$A$2:$A$339)*ISNUMBER(FIND(C4,'as per ledger'!$C$2:$C$339)),ROW($C$2:$C$339)-1))

 

[Khalid NGO]

Hi,

Please try this:

=LOOKUP(2,1/(SEARCH(C4,'as per ledger'!$C$2:$C$339)*('as per ledger'!$A$2:$A$339=A4)*('as per ledger'!$B$2:$B$339=B4)),'as per ledger'!$B$2:$B$339)

Regards,

 

[Shabbo]

Dear Sir,
One more help please, I have found NA entries in statement of accounts but can I know which entries are not coming from as per ledger sheet.
eg: row number : 20 of as per ledger sheet vehicle number : YZ04 EB 5462 not found in statement of accounts sheet.

 

[Khalid NGO]

row number : 20 of as per ledger sheet vehicle number : YZ04 EB 5462 not found in statement of accounts sheet.

Hi,

I didn't found this vehicle number in "as per ledger" sheet.

I have found NA entries in statement of accounts but can I know which entries are not coming from as per ledger sheet.

I am not getting this, please elaborate with few examples.

Regards,

 

[Shabbo]

Dear Sir,
I wanted to lookup which entries are not found in Statement of accounts from as per ledger sheet, using lookup in as per ledger sheet.

 

[Khalid NGO]

Hi,

As now you have identified "not found values" with above posted solutions, you can use this {array formula} in a separate column:

=IFERROR(INDEX($C$4:$C$347,SMALL(IF(ISERROR($D$4:$D$347),ROW($D$4:$D$347)-3),ROW(A1))),"")

{array formulas needs to be entered with
a key combination of Ctrl+Shift+Enter}

or this normally entered:
=IFERROR(INDEX($C$4:$C$347,AGGREGATE(15,6,1/(ISERROR($D$4:$D$347))*ROW($D$4:$D$347)-3,ROW(A1))),"")

Regards,