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 GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

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!