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 |
Solved! Go to Solution.
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?
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.
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)