Identifying knocking off values

Question

I have an excel sheet with values in more than 3000 rows. I have amounts with both negetive and positive signs in these rows. I need to identify the amounts those are knocking off each other(eg : (100) and +100.  )

please suggest a way, a formula or an idea of a macro to identify such knocking off items with less effort or in a short span of time.

Urgent!

Answer 1

One formulas play ... 

Assume source values are running in A2 down

In B2: =IF(A2="","",IF(A2<0,-A2&"_"&COUNTIF(A$2:A2,A2),A2&"_"&COUNTIF(A$2:A2,A2)))

In C2: =IF(COUNTIF(B:B,B2)=2,"x","")

Copy B2:C2 down to the last row of source data

Col C will flag all knock-off items as "x"

Filter on col C for "x" to retrieve the whole lot at one go

Answer 2

.. > I had used ABS formula and sort the amount as per the ABS value,

.. > before using ur formula..

But you don't have to do all of that before using it 

It should work directly on "as-is" unsorted data (real life data)

Answer 3

suppose if i m having values in "A"&"B" AND I HAVE TO KNOCK OFF THE VALUES IN PARTICULAR CELLS BY A CLICK WHAT IS THE SOLLUTION?????

Answer 4

Sorry I have solved this, and the formulas works greats. If you want in order to make it shorter in column B you can write: =IF(A2="";"";ABS(A2)&"_"&COUNTIF(A$2:A2;A2)).

Thanks in advance,

Gisela

Answer 5

I have created a sample file that has a formula to find the duplicate value of the number ignoring the sign.

Let me know if this works for you

to download click here

BLACK BARRON

---

P.S : Please post back with the status of the issue. If this post was helpful to you, please vote for my post If this post has helped you resolve your issue, please mark my post as answered.