Excel Formulas to Clear/Match/Filter Entries
 

I have sorted a spreadsheet by absolute value to match the exact
positives and negatives together. But the information is too large. So
I created nested if statements incorporating "and" and "or" statements
to help match the positive values and negative values.

Here is a sample (x's mean a row's cost center (10650-4) and amount
1,100.90 clears/matches with another row):

37 10650-4 1,100.90 1100.9_37 x
37 10650-4 (1,100.90) 1100.9_37 x
42 10650-4 (1,100.90) 1100.9_42 x
5 10650-4 1,100.90 1100.9_50 x
5 10650-4 1,100.90 1100.9_50

As you can see, these match correctly and show the last line +1,100.90
does not have a match. This works great, throughout some of the
spreadsheet, but not everything.

Can anyone help? I can email you the spreadsheet.

Thanks...

Excel Formulas to Clear/Match/Filter Entries
 

Here is some additional information that might help:

I am trying to reconcile accounts back to 1999 that have over 30,000
lines of entry a month. I only want to keep the row of information
that does not clear. I have been unsuccessful with Subtotals and Pivot
tables because they "summarize" the information. I want the "rows that
net to zero" to be able to be filtered and removed from the data.

The nested if statement I am using is as follows:

=IF(H26=0,"z",IF(AND(J25="x",ABS(H26)=ABS(H25),OR( J24<"x",ABS(H24)<ABS(H26))),"x",IF(H26+H27=0,"x" ,IF(H26+H25+H24+H23=0,"x","
"))))

It seems if there are 5 rows or more of the same value (positives and
negatives), the last part of this if statement,
(IF(H26+H25+H24+H23=0,"x"," "), puts an x by the last line. Showing
that it matches, when in fact it does not have a match. Here's an
example of that:

SysCode CostCntr Amt Concatenate Clears?
37 10701-5 22.83 22.83_37 x
37 10701-5 (22.83) 22.83_37 x
42 10701-5 (22.83) 22.83_42 x
5 10701-5 22.83 22.83_50 x
37 10701-5 22.83 22.83_37 x

In this case the last line is the "one that doesn't clear". A 42 is
the first entry, a 37, the second entry, offsets it. The third entry
is a 5, and a 37 offsets it. Therefore, the last 37 is an erroneous
line and should not have an "x" by it.

Any help you can provide is greatly appreciated.
Thank you.