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

Options

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

  • Katie G
    Katie G ✭✭✭✭
    edited 11/14/23 Answer ✓
    Options

    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

  • AravindGP
    AravindGP ✭✭✭✭✭✭
    Options

    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

  • fryeaf
    fryeaf ✭✭
    Options

    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
    fryeaf ✭✭
    Options

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

  • Katie G
    Katie G ✭✭✭✭
    edited 11/14/23 Answer ✓
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!