How to create if/then formula to identify differences in text columns
I'm trying to automatically identify whether PIs in my departments are collaborating on various projects. I'm essentially looking for a formula that will identify in a column when up to 4 departments are not equal. Suggestions?
Best Answer
-
Hi @fryeaf
You could try something like this -- just counting how many different answers there are across the four columns. If there are more than 1, it means at least one pair is different. This ignores blanks.
=IF(COUNT(DISTINCT([Dept1]@row, [Dept2]@row, [Dept3]@row, [Dept4]@row)) > 1, "Multiple departments", "Not multiple")
Answers
-
Hi @fryeaf
The solution depends on how many departments you have. For example, if you've 5 departments, your formula can be =IF(Department@row <> "5th Department", "Value when the statement is true", "Value when the statement is false"). The symbol <> refers to not equal to.
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training
-
Yes this is doing it! If I have 4 departments, is there a way to create one formula in a cell to check all of them, or do I need to check them individual of each other?
AF
-
And is there a way to account for no difference when the second and subsequent department fields are blank?
-
Hi @fryeaf
You could try something like this -- just counting how many different answers there are across the four columns. If there are more than 1, it means at least one pair is different. This ignores blanks.
=IF(COUNT(DISTINCT([Dept1]@row, [Dept2]@row, [Dept3]@row, [Dept4]@row)) > 1, "Multiple departments", "Not multiple")
Help Article Resources
Categories
Check out the Formula Handbook template!