offsetting a positive value with a negative value in a column

[yasir]

Dear all,

I am taking much time to offset manually the values in a column. I wanted to separate the values which is not offsetting in the column. for example

a

10

-10

20

-20

30


in above example i wanted to separate the 30 using any formula because it is not offsetting with another same value. Please assist if any body knows.

 

[Luke M]

In helper column:

=IF(ISNUMBER(MATCH(-A2,A:A,0)),"corresponding value found","NO MATCH")

 

[kchiba]

You can use conditional formatting with the follwing formula, assuming the data is in the range B6:B30, you can format as required, to highlight the non-matching cells.


=COUNTIF($B$6:$B$30,B6*-1)=0

 

[yasir]

Luke..thanks for ur answer but it is not working if repeated values occurs..


10-

10

20-

20

10

30

in above case i have to separate the 10 & 30. here in your formula the 10 is showing matched but it is not offsetting.

 

[fred]

Have you tried using solver? I have a similar situation at work some time ago and I used solver technique to help me out. Thanks to idea from Hui.


http://chandoo.org/wp/2011/05/11/using-solver-to-assign-item/

http://chandoo.org/wp/2010/10/15/excel-solver-tutorial/


Set your condition so that solver can find a match for you.

 

[Luke M]

Corrected formula for repeat values (starting value is in A2):

=IF(COUNTIF(A:A,-A2)<COUNTIF($A$2:A2,A2),"No match","")

 

[xcruc1at3r]

CAN U Describe the formula so one can learn the concept and the purpose

 

[Luke M]

sure thing xcruc1at3r.

In layman terms, the formula wants to know if there are more entries to date of the same-sign value then there are total entries of the opposite-signed value. This would indicate the that entry does not have a match.


So, the formula does a COUNTIF of the entire range looking for the opposite sign, and compares it to a COUNTIF using a growing range (uses a combined absolute-relative reference) looking for same sign.


If the criteria is met, we flag it with some words, otherwise, leave blank.

 

[ronmaltase]

Corrected formula for repeat values (starting value is in A2):

=IF(COUNTIF(A:A,-A2)&#60;COUNTIF($A$2:A2,A2),"No match","")

I'm trying this...

=if(countif(i:I,-i2)&#60:countif($i$2:i2,i2),"no match","")

but it errors on the #60. What is that #60 for?

 

[ronmaltase]

I'm trying this...

=if(countif(i:I,-i2)&#60:countif($i$2:i2,i2),"no match","")

but it errors on the #60. What is that #60 for?

I noticed I had a colon after the #60 so I changed it...

=if(countif(i:I,-i2)&#60;countif($i$2:i2,i2),"no match","")

I still get an error. And I don't know what the &#60 is for?

-Thanks, Ron M.

 

[ronmaltase]

I noticed I had a colon after the #60 so I changed it...

=if(countif(i:I,-i2)&#60;countif($i$2:i2,i2),"no match","")

I still get an error. And I don't know what the &#60 is for?

-Thanks, Ron M.

OMG...

the &#60 is "<" (less than)

So I used...

=IF(COUNTIF(I:I,-I2)<COUNTIF($I$2:I1960,I2),"no match","")

But...

its marking the positive value with a "no match", but there is a negative match? The negative match isn't marked? it's odd...

 

[NARAYANK991]

Hi Ron ,

Is the address in your posted formula a typo here or is it there in the worksheet as well ?

=IF(COUNTIF(I:I,-I2)<COUNTIF($I$2:I1960,I2),"no match","")

Narayan

 

[Luke M]

(Side note)
When the forum got migrated in 2013, several symbols (such as the < and > symbols) got messed up. As I stumble across old threads, I try to fix them. I'll go through this thread and correct those mistakes, sorry for the confusion this caused you ronmaltase.