How to find matching values

[Feroz90]

Hi All,

I have a data of all positive and negative values like

-400
200
200
-100
100
-500
250
250
300
-150
-150

I have multiple formulas to identify the matching numbers but all i want is modifications

I have data in A1:A100
I use the B1 =IF(COUNTIF(A$1:A2,SUMPRODUCT(--(ABS($A$1:$A$100)=ABS(A2))*$A$1:$A$100))=1,"Target","")
C1 = =IF(SUMPRODUCT(--($B$1:$B$100="target")*($A$1:$A$100=A3))=1,IF(COUNTIF($A$3:$A3,A3)=COUNTIF($A$1:$A$100,$A3),"Flag",""),"")

This will gives me the negative values which has multiple positive values which total the negative value.

For example: A1, A6 but not in A2,A3,A7,A8,A10,A11.

All i want is to get this Target and Flag in B & Column for all the matching values like A1,A2,A3,A6,A7,A8,A9,A10,A11.

Please help me to get this solved.

Thanks in advance

 

[queuesmef]

It's a bit difficult without the actual spread sheet - which could and should be uploaded, however, I would create a helper column, say starting in C, and make it the absolute value of items in Column A. Once this is done, you could run conditional formatting on the itms in Col. C - you may need to get the Unique values from items in C (advanced Data Filtering). Best of luck

 

[Feroz90]

But all i want is if we add the A3 & A4 = 200 + 200 we will get this A2 = 400, so all i want is to update target and flag in B3,B4 & C3,C4

 

[queuesmef]

clear as Mud! Good luck.