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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!