Search code examples
excelif-statementexcel-formulaexcel-2013worksheet-function

Removing opposites from Excel

I am trying to figure out a way to remove values that have a net result of zero. For example I have data that is 10,000 and -10,000. They are linked to the same person though not the only transaction linked to a single individual.

Is there a method to remove both of these rows from my Excel document?

Here is a the kind of data I'm looking at

Here is a the kind of data I'm looking at

And I want to remove all but the last row as it is the only unique one.

Solution

  • If you create a helper column with =ABS(A1) copied down and then sort on ColumnB, that helper column and within that on ColumnA - you should end up with sets as you already have pairs. Then in another helper column Row2 and copied down: 

    =IF(OR(A1=-A2,A2=-A3),"#","")

    should flag 'equal and opposites' next to one another. Remove those and repeat until no more flags appear, if you have multiple instances of equal and opposite values. Beware however where the last value for one Cust # is equal and opposite to the first value of the next Cust #.