Nov 13 2019 07:41 AM
Nov 13 2019 07:41 AM
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 -
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:
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!
Nov 14 2019 01:22 AM
Oct 11 2023 01:44 PM
your formula is not working in my data since there are repetitive amounts of the same number
Oct 11 2023 04:28 PM
You do realize that just shy of 4 years (!!) have passed since the original question and the reply of @Jan Karel Pieterse ?
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.