VBA Highlight duplicate ID which sum of their value equal zero

[Aicha]

Dear Community
I need a formula that highlight values which total is zero but have the same reference ID
I want to have the below results.

[TABLE="width: 289"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Value[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: center"]1[/TD]
[TD]highlighted on color green[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: center"]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: center"]-1[/TD]
[TD]highlighted on color green[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: center"]4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: center"]-2[/TD]
[TD]highlighted on color green[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: center"]2[/TD]
[TD]highlighted on color green[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]

I will really appreciate if you can support
Thank you in advance.

 

[Mentor82]

Hi,
You cannot highlight the cell by the formula itself. You have to use either condional formatting or VBA code.
I recommend you to set the conditional formatting. Select range of column with result only withouth headers - let's say it's range C2:C100. Then place below formula and set formatting colour you prefer.

=SUMIFS($B$2:$B$100;$A$2:$A$100;$A2)>0

 

[Aicha]

Dear

Thank you for your replay, if their is a VBA code solution that would be great. but I didn't find it. Iam not yet expert, I just start learn it. if I find the formula then I can record it as a macro.
Unfortunately, The formula you provide is not working.
I know that I should use 2 formula and combine them.
1) COUNTIF($A$1:$A$10;A2)>1 to find duplicat in ID row
2) ifCOUNTIF($A$1:$A$10;A10)>1;IF(SUM(A1:A10)=0;"Duplicates";"No duplicates")

My goal is to highlight open items at vendor

 

[Aicha]

My goal is to highlight open items at vendor account that have the same reference but total equal zero
I use ABS formula to have absolute value then I use the formula IF(SUMPRODUCT(($A$1:$A$10=A1)*1;($B$1:$B$10=B1)*1)>1;"Duplicates";"No duplicates")
And then check manually if total is zero or not
I use also Pivo which is great to find these items but Iam looking for a quick solution

Thank you

 

[Mentor82]

Hi,
Frankly you not really check the total sum =0 against id. A=1, A=2, A=-1, the total is 1+2-1=2. But now I get your point. Chech out below formula. Set the conditional formatting in result column C (C2:C100), set the colouring and place below formula.

=or(and($A2>0;COUNTIFS($B$2:$B$100;$A2*(-1));and($A2<=0;COUNTIFS($B$2:$B$100;$A2*(-1)))

 

[Mentor82]

Small correction, because I made previously a mistake.

=or(and($A2>0;COUNTIFS($B$2:$B$100;$A2*(-1))>0);and($A2<=0;COUNTIFS($B$2:$B$100;$A2*(-1))>0))

 

[Mentor82]

Hi,
Get a rid of the previous formula I made for you - it will not work properly. I've checke that on laptop. Here's the right formula which works. It works in range from row 2 to 100. It checks ID column A row 2 on and picks the value from column B and search for the opposite if exist related to the ID column A. If finds, it colours the cell in column C. So, set the conditional formatting selecting range, only column with result - below works when you select C2:C100 and place below formula. Having copied and pasted the formula confirm it with ctrl+shift+Enter

=MATCH($A2&$B2*(-1);$A$2:$A$100&$B$2:$B$100;0)

 

[Mentor82]

Hi,
Get a rid of the previous formula I made for you - it will not work properly. I've checke that on laptop. Here's the right formula which works. It works in range from row 2 to 100. It checks ID column A row 2 on and picks the value from column B and search for the opposite if exist related to the ID column A. If finds, it colours the cell in column C. So, set the conditional formatting selecting range, only column with result - below works when you select C2:C100 and place below formula. Having copied and pasted the formula confirm it with ctrl+shift+Enter

=MATCH($A2&$B2*(-1);$A$2:$A$100&$B$2:$B$100;0)

Hi again,
For the conditional formatting you do not have to confirm the formula ctrl+shift+enter but on OK (this is an array formula which has to be confirmed in cells that way but in the conditional formatting it works without such confirmation)

 

[Aicha]

Hi,

Thank you for your support
You answer is near to the result Iam looking for but, it doesn't work properly because for example if I have ID : A= 1, A=-1 and A=1 the total is 1 not zero and with this formula the 3 numbers are highlighted

 

[mikerickson]

Try the ConditionalFormat

=COUNTIFS($A$1:$A$10, A1, $B$1:$B$10, -1*B1)>0

(There will be a problem if there are three rows whose ABS is the same)