Searching Two Columns for Two Values that Sum 0 Then Removing Both

I have a dataset where two columns for the most part have values that do not interact; however, occasionally there are two numbers (positive and negative) that will cancel each other out equaling zero. My goal is to find all the sets that cancel each other out (removing both), leaving only the non canceled ones. Here's an example of what I mean:

Record ID A B

1 20

2 (8)

3 (20)

So in this case I'd like to search through all the columns and remove 1 and 3 since 20 + (20) = 0. Since 2 isn't canceled, I would leave it there.

Datasets

Developer

Developer Tools

Accepted answers

gabrielvilella

Hi @Jc_KPMG, I build a solution that will work if you have the same number multiple times on either column.

Remove opposites.yxmd

All comments

gabrielvilella

Hi @Jc_KPMG, on your dataset, is it possible to have two number 20 on A and one -20 on B, so you have to remove just one occurence of the 20 from A? If so, how do you determine which number 20 to be removed from A?

Jc_KPMG

It's certainly possible, but the goal is to not double count the values since inevitably I will be adding all the values together (which will not be combined together, so all positive added together and negatives added together amongst themselves). Each value will be unique so in your example, the second (20) would have a unique identifier different than the first (20).

ArtApa

Hi @Jc_KPMG - A lot will depend on your actual data, but based on my understanding here is what you can do:

Sum0AA.yxmd

gabrielvilella

Hi @Jc_KPMG, I build a solution that will work if you have the same number multiple times on either column.

Remove opposites.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703