How to find matching values

Feroz90

Board Regular
Joined
Apr 25, 2019
Messages
52
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
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

queuesmef

New Member
Joined
Dec 19, 2016
Messages
25
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
Joined
Apr 25, 2019
Messages
52
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,396
Messages
5,636,059
Members
416,896
Latest member
Hozier

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top