Comparing negative and positive values

December 13, 2013 at 16:42:07
Specs: Windows xp
I have a large spreadsheet 3000 rows how can I match the numbers that are the same except one is a positive number and one is a negative number and highlight the one that matched leaving only the ones unmatched not highlighted or moved to another column in excel worksheet thanks sharon

See More: Comparing negative and positive values

Report •

#1
December 14, 2013 at 16:23:40
Let's say you start with this:

         A
1       25
2       29
3      -25
4       63
5       42
6      -63
7       45
8       52
9      -52

Enter this in B1 and drag it down.

=IF(ISNA(MATCH(-A1,$A$1:$A$9,0)),A1,"")

You should get this:

         A     B
1       25
2       29    29
3      -25
4       63
5       42    42
6      -63
7       45    45
8       52
9      -52

The MATCH function will return a #N/A error if the lookup_value isn't found in the array. Since the lookup_value is the "negative" of the value in Column A, if no matching negative value is found, the ISNA function will be TRUE and the Column B cell will show the value in the corresponding Column A cell, otherwise it will remain blank.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
December 15, 2013 at 13:03:43
Just for a bit or variety, here is another way.

Using DerbyDad03s example, in cell B1 enter the formula:

=IF(SUMPRODUCT(--(ABS($A$1:$A$9)=ABS(A1)))>1,"",A1)

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
December 16, 2013 at 06:11:23
Derby dad the formula worked partially. If I have multiple negative or positive numbers it assumes all me the criteria and does not carry the additional neg or positive numbers over so each value can be used only once. For instance
+25
+25
-25
-25
-25


I would expect the extra -25 to move to col b and it does not


Report •
Related Solutions


Ask Question