Snasui Excel Forum

Hi there
Say hi to everybody here.
I am working in an accounting unit,I need formula to match dr and cr in two different columns and extract different amount ( amount not yet cleared).

I attach a sample for your reference

Thanks

sna wrote: Sat Jul 09, 2022 10:25 am Hi there
Say hi to everybody here.
I am working in an accounting unit,I need formula to match dr and cr in two different columns and extract different amount ( amount not yet cleared).

I attach a sample for your reference

Thanks

Hi sna, Can you explain more condition or information that's How are they come (Results)

...

B23

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$20)/ISNA(MATCH($B$2:$B$20,$C$2:$C$20,0))/($B$2:$B$20<>""),ROWS(B$23:B23))),"")

**
The formula based on all credit numbers would be correct, just need to find out the unmatched only the Debit values.


Norkaz

Thank Nokaz,it is working

I have another question amount correct 4,065.60
but accountant booked Dr 4,065.50 then he adjusted add amount 0.10 on Dr too when someone cleared amount 4,065.60 in Cr side how can we write formula to acknowledge this amount on Cr equals to Dr if Dr has two lines but Cr one line.

Thanks

...

Once the booked values of the Debit side have more than one transaction by total amount would be matched to the credit side, this case needs more criteria to match the equal numbers of debit and credit sides such as the transaction number/transaction reference, etc would be same.

Norkaz