How to find matching values

Feroz90

Board Regular
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.

Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

queuesmef

New Member
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

Board Regular
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

New Member
clear as Mud! Good luck.

Replies
3
Views
58
Replies
20
Views
228
Replies
2
Views
61
Replies
6
Views
87
Replies
7
Views
278

1,129,766
Messages
5,638,236
Members
417,016
Latest member
Tegguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back