PDA

View Full Version : Solved: Finding opposite entries.



Marcster
01-20-2006, 05:07 AM
Hello people,

I have a list of amounts like:
124.69
108.83
59.4
120.52
41.49
41.09
19.8
70.51
168.3
-124.69
-108.83
In column I.

Is there a formula (to be placed in column J) which returns false
(or anything else) if there is no opposite entry amount in column I?.

For example, the first amount 124.69 has a minus -124.69
in the list, so this should return TRUE.
The amount 59.4 doesn't have a minus amount so should FALSE.

Any ideas please?,

Thanks,

Marcster.

Bob Phillips
01-20-2006, 05:53 AM
=ISNUMBER(MATCH(-A1,I:I,0))

Marcster
01-20-2006, 06:03 AM
Hi XLD,

That doesn't work.

I have data in columns A:I
But it's just the amounts in column I i'm interested in.
Ive tried =ISNUMBER(MATCH(-K1,I:I,0))

Marcster.

Bob Phillips
01-20-2006, 06:11 AM
Hi XLD,

That doesn't work.

I have data in columns A:I
But it's just the amounts in column I i'm interested in.
Ive tried =ISNUMBER(MATCH(-K1,I:I,0))

Marcster.

Are you trying to identify any number in A:I that has a negative value. If so, I would use conditional formatting with a formula of



=COUNTIF(A$1:$H400,-A1)=0

tpoynton
01-20-2006, 06:17 AM
what about =ISNUMBER(IF(I1>0,MATCH(-I1,I:I,0))) ? that way only positive #'s are evaluated...

Marcster
01-20-2006, 06:21 AM
What I have is a list of data 1,192 rows covering columns A:I
In column I i have various amounts, postive and negative amounts.
Most of them will cancel each other out.
?124.69 and -?124.69
but there will be some with no negative or positive entry.
A ?59.40 doesn't have a -?59.40 in column I.
A -?41.59 doesn't have a ?41.59 in column I.
I need to find all these. A formula or validation in column J?.

Thanks for your help,

Marcster.

tpoynton
01-20-2006, 06:28 AM
then I believe that XLD's original formula basically works:

=ISNUMBER(MATCH(-I1,I:I,0))

this returns false if the opposite of the # is not in column I...at least it does in the attachment!

Marcster
01-20-2006, 07:11 AM
That works :).

Don't know what I did earlier :bug:.

Thanks,

Marcster.