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

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 -52Enter 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 -52The MATCH function will return a #N/A error if the

lookup_valueisn't found in the array. Since thelookup_valueis 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

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

message edited by mmcconaghy

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

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History