# Put duplicates values at zero

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

• Please note that your question requires that any answer (like this solution) keys off the order of your data. I recommend inserting an additional index column numbered from 1 to x so that if you ever need to sort your data (or do it accidentally), you can restore the original order based on that column. Feb 10, 2023 at 18:50

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
)
)
)
)
``````
• It's working! THANKS A LOT MAN! Feb 10, 2023 at 19:57

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)))
``````