Put duplicates values at zero

Question

I'm having some trouble finding a formula to put(replace) my duplicates values into "0". I tried everything I know (not a lot) and nothing works. I have two columns: Column A and Column B. Column A is a list of References numbers and Column B is the "amount to pay". Some rows in duplicate in column A have the same value assign in column B. All I need is replace (not delete) the duplicate columns B value for a "0" in a new column and this for all the items (so looking for an automatic formula)... I'll show you and I hope you can help me...

Answer 1

Put this formula in cell C2:

=if( countifs(A$2:A2, A2, B$2:B2, B2) > 1, 0, B2 )

The formula needs to be copied down the column.

You can also do the same with a lambda that fills the whole column in one go, like this:

=let( 
  keys, A2:A, values, B2:B, 
  firstKey, offset(keys, 0, 0, 1, 1), 
  firstValue, offset(values, 0, 0, 1, 1), 
  map( 
    keys, values, 
    lambda( 
      key, value, 
      if( 
        countifs( 
          firstKey:key, key, 
          firstValue:value, value 
        ) > 1, 
        0, 
        value 
      ) 
    ) 
  ) 
)

Answer 2

You can try this. With MAP it counts if there is more than 1 value equal in those columns up to each row. If there is, it returns 0, if there isn't it returns the value in column B:

=MAP(A2:A,B2:B,LAMBDA(aval,bval,IF(COUNTIFS(A2:aval,aval,B2:bval,bval)>1,0,bval)))