finding out pairs of positive & negative numbers!

[via135]

i want to list out the pairs of postive & negative numbers from a list.


for ex:
A1:A13 having values as

100
-400
200
300
-100
-500
-100
-200
-200
300
-300
-300
300

i want to list out the unique numbers excluding the pairs of "+" &
"-" numbers such as

A2 -400
A6 -500
A7 -100
A9 -200
A13 300

help pl?!

 

[Dave Peterson]

One way:

Insert a helper column (say column B)
Add headers in B1
Then put this formula in B2 and drag down:
=abs(a2) (if your data is in column a)
Then select that range of helper formulas and convert it to values.
Edit|copy
edit|paste special|Values

Now select the header row through the bottom of your data (B1:Bxx)

Then data|filter|advanced filter.
check the unique records only box

And you'll have your list.

 

[Pete_UK]

This isn't quite what the OP wanted, Dave. Your approach will give him
the first record of a particular magnitude, as follows:

100
-400
200
300
-500

rather than matching up (and removing) pairs of +ve and -ve numbers.
However, if after the first part of your procedure you sort the data by
column B then by column A you will get:

-100 100 <--a
-100 100
100 100 <--a
-200 200 <--b
-200 200
200 200 <--b
-300 300 <--c
-300 300 <--d
300 300
300 300 <--d
300 300 <--c
-400 400
-500 500

Visually inspecting this reveals where the pairs are - marked a, b, c,
d above. I don't know enough VBA to code this, but I can see the
procedure being to scan through column B, identifying the start and
finish of a range of equal-magnitude values; if start = finish then
move on to the next magnitude (only 1 value), otherwise if the values
in column A at start and finish are equal then delete the cells from
start+1 to finish and set finish=start (i.e. all duplicates of the same
value - the OP does say "unique" values that are not matched),
otherwise delete the cells at start and finish (pair of +ve and -ve),
reduce finish by 2 and continue checking until start = finish.

Anyone like to pick this up from here?

Pete

 

[Guest]

Try this:

Using your list of number in Cells A1:A13

B1:
=IF(COUNTIF(A$1:A1,SUMPRODUCT(--(ABS($A$1:$A$13)=ABS(A1))*$A$1:$A$13))=1,1+ROW()*0.001,10^10)
Copy that formula down to cell B13

D1: 1
D2: 2
Continue the sequence down to D13

E1:
=IF(SMALL($B$1:$B$13,D1)=10^10,"",INDEX($A$1:$A$13,MATCH(SMALL($B$1:$B$13,D1),$B$1:$B$13,0)))

Copy that formula down to Cell E13

Here are the returned values:
E1: -100
E2: -200
E3: 300
E4: -400
E5: -500
E6:
E7:
E8:
E9:
E10:
E11:
E12:
E13:

Is that what you're looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

If you just want the unique non-offset number identified....
Put this formula in Cell B1 and copy down:
=IF(COUNTIF(A$1:A1,SUMPRODUCT(--(ABS($A$1:$A$13)=ABS(A1))*$A$1:$A$13))=1,A1,"")

If you want them flagged:
=IF(COUNTIF(A$1:A1,SUMPRODUCT(--(ABS($A$1:$A$13)=ABS(A1))*$A$1:$A$13))=1,"Flag","")


Does either of those help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Max]

Here's another play to try ..

Sample construct available at:
http://www.savefile.com/files/3770366
Eliminating_Pairs_of_Pos_n_Neg_Numbers_via135_gen.xls

Assume source data is in A1:A100

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

Put in C1, and array-enter the formula,
i.e. press 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()))

Put in D1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROWS($A$1:A1)),C:C,0)))
(Normal ENTER will do)

Select B11, fill down to D100
to cover the max expected data in col A

Col D will return the desired results neatly bunched at the top,
viz. for the posted sample data in A1:A13, we'd get:

-400
-500
-100
-200
300

Adapt the ranges $A$1:$A$100, $B$1:$B$100 in the formula in C1
to suit the extent of the source data in col A. Note that we can't use
entire col references (e.g.: A:A, B:B) in the array formula in col C.

 

[Max]

.. Anyone like to pick this up from here?

Perhaps the formula options in Ron's responses and mine ? <g>

 

[Dave Peterson]

Ahhh.

I misread the OP. It looked like he/she just wanted a list of unique values
(without regard to sign).

Now I see that the cells that are additive inverses should be ignored.

 

[Guest]

Select Cells A1:A100 (with A1 as the active cell)
Format>Conditional Formatting
Cell Formula is:
=COUNTIF(A$1:A1,SUMPRODUCT(--(ABS($A$1:$A$100)=ABS(A1))*$A$1:$A$100))=1
Set the conditional fill color (I used bright yellow)

That will highlight all unique non-offset numbers in the list.

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[via135]

hi RON

thks first!
as suggested by you i am able to get the correct net result using your
second formula(marking flag). but still the result is not in the order
i am expecting. ie) the first postive value should offset the first
negative value, the second positive with the second negative..and vice
versa.

customer amt result getting result
expecting
a 100 -400 b -400 b
b -400 flag 300 d -500 f
c 200 -100 e -100 g
d 300 flag -500 f -200 i
e -100 flag -200 h 300 m
f -500 flag
g -100
h -200 flag
i -200
j 300
k -300
l -300
m 300

hope u understand!

-via135

 

[via135]

hi RON!

thks first!
as suggested by your second formula (flagging) i am able to get the
correct net result...but still the result is not in the exact order i
am expecting. my expectation is that the first positive number should
offset the first negative number, the second postive with the second
negative..and vice versa.


hope u understand!


via135

If you just want the unique non-offset number identified....
Puft this formula in Cell B1 and copy down:
=IF(COUNTIF(A$1:A1,SUMPRODUCT(--(ABS($A$1:$A$13)=ABS(A1))*$A$1:$A$13))=1,A1,"")

If you want them flagged:
=IF(COUNTIF(A$1:A1,SUMPRODUCT(--(ABS($A$1:$A$13)=ABS(A1))*$A$1:$A$13))=1l"Flag","")


Does either of those help?

***********
Regards,
Ron

XL2002, WinXP-Pro

[/QUOTE]

 

[Guest]

I think I got it this time:

B1:
=IF(COUNTIF(A$1:A1,SUMPRODUCT(--(ABS($A$1:$A$100)=ABS(A1))*$A$1:$A$100))=1,"target","")
Copy down through B100

C1:
=IF(SUMPRODUCT(--($B$1:$B$100="target")*($A$1:$A$100=A1))=1,IF(COUNTIF($A$1:$A1,A1)=COUNTIF($A$1:$A$100,$A1),"Flag",""),"")
Copy down through C100

Is that it?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[via135]

thks mr RON!

thks a lot..this is what i exactly want!-

via135