How do I compare two multiselect text columns and return what isn't in both in a third column?

Options

I'm using the Back to Work Training Template.

Trainings Assigned is a manual multi-select drop down column.

Trainings attended is also multi-select drop down column with this equation: =IF(ISBLANK(JOIN(DISTINCT(COLLECT({EmployeeTrainings}, {EmployeeEmail}, [Employee Email]@row)), CHAR(10))), "No Trainings Attended", JOIN(DISTINCT(COLLECT({EmployeeTrainings}, {EmployeeEmail}, [Employee Email]@row)), CHAR(10)))

What I want is a third column that compares Trainings Assigned with Trainings Attended and returns "Trainings Needed" So basically Trainings Assigned - Trainings Attended = Trainings Still Needed.

I think I need to be using the HAS function but I'm otherwise pretty lost. Any help is greatly appreciated!

Thank you,

Olive

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @orenwick

    Make sheet with list if all trainings (If you don't already have).

    Then your formula would be:

    =JOIN(COLLECT({Trainings}, {Trainings}, AND(HAS([Trainings Assigned]@row, @cell), NOT(HAS([Trainings Attended]@row, @cell)))), CHAR(10))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!