Trying to reconcile a column

[michaelcooper]

I have approximately 3000 lines of data that includes both positive and
negative numbers. This represents a clearing acount that does not clear to
zero as it should. Of the 3000 lines I have approx 100 of these that are
negative numbers. I need to find out what Positive numbers match the
negative numbers to zero when added. There are no formulas in this sheet.
Can Excel give me that answer?

 

[jb_tenor1]

Using column 'A' as the source of the 3000 numbers, you could try this
formula in column 'B'. A1 and B1 contain the headers, so this formula you
could enter into cell B2:

==IF(A2<0,IF(ISERROR(VLOOKUP(A2*-1,A:A,1,FALSE)),"",VLOOKUP(A2*-1,A:A,1,FALSE)),"")

The formula looks at the value in A2 to see if it is negative and then looks
to see if it can find the opposite value by leveraging a vlookup. If it
finds a match, it puts the positive number in column 'B' next to the negative
number. Since you only have 100 negative numbers, this could be a useful way
to inspect the numbers. However if you have a lot of negative numbers with
duplicate values, you will have to find a different approach.

Here's a quick example of the output I received from trying this against a
small list:

Number Match?
100
-30 30
30
-50
25
10
75
-25 25

Jeff