Delete rows of negative and positive cells that cancel each other out - Exact Opposites

Copper Contributor

Is there a way that I could delete rows that are exact opposites & net to zero? Keeping only the numbers that are affecting the overall balance. 

 

For example - 

-100

-200

50

100

200

 

In that case only the 50 is causing any actual change in the set of numbers. The +-100 & +-200 cancel each other out. In this example it's easy to to see - but I'm looking at a spreadsheet that is thousands of rows long. I want to narrow it down by deleting rows that net zero. 

 

I want to find EXACT opposites and NOT combinations that net zero.  See in example below:

-20

10

10

20

 

I would like the +- 20 to go away, but keep the 10s open. I do not want the two +10 to net with the one -20. Does that make sense?

 

Thanks everyone for your time and help!

 

Kind Regards, 

 

-Mark-

3 Replies
Suppose you have A2:A1000 filled.
Enter this formula in any adjacent column:
=COUNTIF($A$2:$A$1000,-A2)
Copy the formula down to match the # of rows filled
Filter the table to hide zeroes (uncheck zero in the list or select "Number filters", "Not equal to" and enter a 0).
Delete all visible rows and remove the filter.

@JKPieterse 

 

your formula is not working in my data since there are repetitive amounts of the same number 

@pssarna_27 

 

You do realize that just shy of 4 years (!!) have passed since the original question and the reply of @JKPieterse ?

 

It might make more sense in this case to start afresh, especially since it sounds as if you have a slightly different starting point. If it's possible for you to do this, you could help us help you by posting a copy of your workbook (or a mockup that replicates the circumstances), putting the file  on OneDrive or GoogleDrive and pasting a link here that grants access.

 

While you're at it, I'm curious -- this applies to the original question posted by @Mark0987 four years ago as well -- if the numbers are cancelling one another out, what difference does it make? Asked a different way, what is it about the circumstance that makes it important to eliminate some of the numbers from the sum? I ask because I'm a person who always wants to be able to go back to the starting point, to verify or validate a process, to check the historical record. Eliminating actual data does away with that possibility, and could be problematic if, for example, one (or more) of the eliminated rows turns out to have been inaccurate in the first place and should have been retained.