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

[Jc_KPMG]

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.

[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:

[gabrielvilella · Accepted solution]

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