How to create if/then formula to identify differences in text columns

fryeaf

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?

Katie G

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

AravindGP

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.

fryeaf

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

fryeaf

And is there a way to account for no difference when the second and subsequent department fields are blank?

Katie G

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