Please help. I have a large spreadsheet about 1500 rows. This is how the amount column looks like

123

-123

554

-333

-554

145

-145

234

My question is how can I match the numbers that look the same, except one is a positive number, and the other is a negative number ( ex:123 and -123) Once these numbers are identified I want to delete both of them and at the end my total will be left with only numbers that can't pair with other numbers.Your help in this matter is greatly appreciated.

Chom

Hi, The Excel function you want is ABS()

To compare two numbers in cells A1 and A2 irrespective of sign, use this formula:

=IF(ABS(A1)=ABS(A2),"Equal","Not equal")If you want help on the rest of the issue, some more information would help.

Are there ever more than one match, say:123 -123 -123

If so how are these handled.

Do you want your list of unmatched values in a different location, or do you want them in the same place.Is this a one time or occasional process where it is OK to go through a few steps to get the results,

or

Is this a frequent process that would benefit from automation with a macro (with it's associated overhead of entering code, and managing it if circumstances change)Regards

Your help is greatly appreciated, but I think I didn't make it clear on my questions. Here's the sample of my worksheet:

Column A

123

-123

554

-333

-554

145

-145

234Added all numbers above give me a total of - 99

I need a formula that goes look for numbers that are the same irrespective of sign so I can total this column to give me -99. When I use your function, it doesn't give me the result that I am looking for. Please help me further with this problem. Your help is greatly appreciated

Hi, Your response is very confusing.

You say that your column of numbers comes to -99, which is correct.

You then sayI need a formula that goes look for numbers that are the same irrespective of sign so I can total this column to give me -99Well, as the column totals -99 anyway, what is the point of finding 'matching' numbers such as 123 and -123

If you add 123 to -123 the result is zero, so there is no point in matching them or removing them before creating the total.

If you values are in cells A2 to A9,

then enter this formula in any cell:

=sum(A2:A9)

It returns -99Regards

Hi Humar, I am after a similar macro....

I have a sheet of data, looks like the following (although i have sorted it to date range and usually its totally out of order.) - the sub job and description vary too.

Sub Job Task Date Description Qty Billable Cost050 0 05/11/10 E166 (1.00) AUD (106.50) (78.00)

050 0 05/11/10 E166 1.00 AUD 106.50 78.00

I would like a macro that matches the negative values to the identical positive values - (like the data above) and delete them, as they cancel each other out...If you could help, that would be wonderful!

Thanks,

Sam

Please repost your data after reading the How To referenced below. That should make it easier for us the read.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Ask Your Question

Weekly Poll