1

Trying to compare 4 fields (only if populated) against one another so that no data is duplicated? I've tried something like this but gets too long.

=IF(LEN(f1=1), IF(LEN(f2=1),f1<>f2,FALSE),FALSE)
=IF(LEN(f1=1), IF(LEN(f3=1),f1<>f3,FALSE),FALSE)
...

Can you show me a better way to do this?

0

The formula will be long and there is no way to escape it. If possible you should do this in an event receiver.

=IF(ISBLANK(f1),TRUE,IF(ISBLANK(f2),TRUE,IF(ISBLANK(f3),TRUE,IF(ISBLANK(f4),TRUE,IF((f1=f2)OR(f1=f3)OR(f1=f4)),TRUE,IF((f2=f3)OR(f2=f4),TRUE,IF(f3=f4,TRUE,FALSE)))))))

The logic is a follows

  1. If any of the field is blank then return true
  2. else
    • if f1 = f2 or f1 = f3 or f1 = f4 then return true
    • else if f2 = f3 or f2 = f4 then return true
    • else if f3 = f4 then return true
    • else return false

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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