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...
Put this formula in cell
=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 ) ) ) )