Offset debits and credits - p/o

[leebird]

Hello,


I have an excel spreadsheet with debits and credits I have to manually offset. I have attached my document

What I would like to do is if a p/o (column H) has an offsetting debit and credit, or that same p/o has multiple debits and credits that offset-----> highlight these lines one colour

It gets tricky in that the following criteria should be met, If the p/o has a remaining amount on it, that remaining amount should be from the last TRX Date (the most recent date row should remain on the spreadsheet).
(please see offset spreadsheet and disregard accrued pop)
If possible these remaining lines should be highlighted another colour

I'm looking for a formula that can help me with this.

Thank you very much

[Tsjallie]

if a p/o (column H) has an offsetting debit and credit, or that same p/o has multiple debits and credits that offset-----> highlight these lines one colour

Is this what you mean for this part?

Looking at the second part yet.

[Tsjallie]

How would you handle the bookings in this example?
The entries with the letters A, B and C are actually offsets.
Could they be coloured green and the remaining entries red?
Or should these offsets be disregarded?
Offsets.JPG

the most recent date row should remain on the spreadsheet

Do you mean the other entries should be deleted or hidden?

[leebird]

Hello, Yes you are correct that is what I meant ------> highlight these lines one colour

[leebird]

Hello,

In your example they would be coloured green (to align with the other offsets). The remaining entries with no offsets can be red. I would prefer to keeping them on the sheet as opposed to being hidden or deleted please.

Thank you so much for your help.

[Tsjallie]

Ok, it indeed is a bit tricky. Need to chew on that.
Would you oppose to a little piece of vba? Just in case.

[Tsjallie]

Think I figured it out. And without turning to vba :-)
The colouring is done thru Conditional Formatting.
The formula for the CF however is in a helper column ("Matched") to make the CF faster (or rather less slow).
I also introduced 2 helper columns to hold the instance of an amount within it's po.
This is necessary to make a match when there are multiple (equal) amounts to match with.
For instance if within a po there is a record with $ 100 credit and 2 or more records with $ 100 debit.
In that case there is no identifier in the table to support choosing a debit amount other than the TRX date.
But using the TRX date would make things very complex.
Hope you're ok with using instances for the solution of that.
The formula is in the column "Matched" for which I made a named range to use in the CF- formula.
See notes in the sheet for further details.

It doesn't delete or hide any rows. Filtering on red color will leave only the unmatched records visible.

[leebird]

Amazing!! Thank you very much. The trx date was the tricky part but I think your solution works. I very much appreciate your time spent on this. Thank you again!