Filter for offsetting entries with no unique identifier

I have a dataset that I want to remove offsetting entries from, which means two values that sum exactly to zero. However, there isn't an unique identifier to group the data in so I just want the criteria to be the values alone. The offsetting entries wont be beside each other like in my example and could exist anywhere in my table. So as long as it finds an offsetting entry in the table and each entry can only be offset once. Thanks in advance!

Example:

Client	Amount
A	100
B	-100
C	200
D	100
E	-200
F	-200

Desired result:

Client	Amount
A	100
B	-100
C	200
E	-200

Transformation

Accepted answers

apathetichell

slightly different approach - tile tool.png

In the even that @atcodedog05 's version doesn't fully do what you need - take a look. This one has a slightly different approach. (Tile Tool)

matched amounts.yxmd

All comments

atcodedog05

Hi @overhead_press

Here is how you can do it.

Workflow:

Hope this helps : )

match up.yxmd

apathetichell

In your scenario is it true that the first entry of a value is always the match entry (ie C matches with E - not F). There has to be a logic to that. Also are there repeated matches (ie multiple 200s and -200s)- each of which match?

overhead_press

It doesn't have to be, it just needs to pair with an offsetting value (ex. 300 and -300). And yes there could be repeated values (Ex. Multiple 300s and -300s) but each row can only be matched/offsetted once.

apathetichell

slightly different approach - tile tool.png

In the even that @atcodedog05 's version doesn't fully do what you need - take a look. This one has a slightly different approach. (Tile Tool)

matched amounts.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703