Mr.Max,
Pl see my response to this question. I shall be grateful for your comments
/ suggestions.


"Max" wrote:

One play which can deliver both the CF desired and a way to extract the
cells in col A which do not cancel out ..

Assuming source numbers within A1:A100, as posted

Put in B1:
=IF(A1="","",COUNTIF($A$1:A1,A1))

Put in C1, then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(B1="","",IF(ISNUMBER(MATCH(-A1&"_"&B1,$A$1:$A$100&"_"&$B$1:$B$100,0)),"",ROW() ))
Select B1:C1, copy down to C100. Col C will "flag" only cells in col A which
do not cancel out.

Then you could easily apply CF to highlight col A pointing to col C
Select col A (A1 active), then apply CF using Formula Is:
=$C1""
Format the fill color to taste, OK out

And if you want to extract those cells in col A which do not cancel out in
another col (this might be useful), just put in say E1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))
Copy down to E100. This will return all cells in col A which do not cancel
out, neatly bunched at the top in col E
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
ps.com...
Tom,
A great start upon a solution, i am attempting a workaround using the
info you provided, many thanks!

Although my problem is more intricate than i let on. Realistically, i
would like to find duplicate values within the same column, but some
of the values are negative and some ar positive. I need to find out,
and highlight, the values which would cancel each other out. Hence i
need to find duplicates, except that they are not EXACT duplicates,
but one is the positive version of the number and one is the negative
version of the number. Here is an small example of such a list:

A1
10 -matches such as this and the negative 10 below it both need to
be highlighted
-10
11
12
13 -does not cancel and doesnt need to be highlighted
-12
-11
15
-15
-15 -also does not cancel since one positve already canceled with one
negative and doesnt need to be highlighted

as you can see, i am attempting to highlight the corresponding
positive and negative values (the ones which would cancel each other
out) and leave the singular values unhighlighted. The problem is
even futher complicated by the fact that some values might have
multiple duplicates. There may be 10 positive number 11's and
nineteen negative number 11's, meaning i need the 10 positive versions
of the number 11 to cancel and highlight along with the 10 negative
versions of the number 11, but to leave the other 9 number 11's
unhighlighted.

I apologize for how confusing this sounds, but i would choose
confusing over hours of manual labor infused with human error anyday,
seeing as how i have thousands of rows within this one column with
many random and changing values to identify and compare.

If you or anyone can think of a solution more closely tailored to this
particular situation, i would be indebted to you forever.

In the meantime, i will work with what you provided to me Tom.

Thank you kindly.