0

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

EXAMPLE

1
  • 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.
    – Blindspots
    Feb 10, 2023 at 18:50

2 Answers 2

0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.