How to remove two rows based on their specific values i.e. remove a payment that later gets refunded

[JollyRoger01]

I am trying to reconcile PayPal statements against a bank account and just want to get a list of incoming and outgoing transactions. To do this I need to weed out the refunds from that occur at a later date.

I'd like to write some code that goes something like this:

If current row name contains "Refund" then record that amount value.

Find another row with the opposite value.

Delete both lines.

Repeat for all lines.

Does anyone know how I write that?

[Anonymous · Accepted solution]

Hi @JollyRoger01 ,

I created this sample data.

The result is this.

The method is to first find out the number with Refund, and then add a custom number to turn the found out number into the opposite. Then add the opposite sequence as a new query.

Turn the list into a table. Then use merge to merge the two tables, filter and delete redundant columns.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JollyRoger01]

That's perfect, thanks so much for your help on this!

This makes my transaction records more accurate as I'm not recording credits and debits that didn't really occur.

[AlB]

Hi @JollyRoger01 

Do you want to show some sample data and the expected result?

I'm guessing you'll have to do something more than just look for a row with the opposite value. What if there are values that appear more than once, which one does the refund belong to?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.