Compare two columns and input missing items into a third column

Options

I need help with a formula that compares "Training Assigned" and "Training Attended". If they match then I would like the "To Be Completed" column to say "Complete" but if they do not match I would like the missing training to be listed.

"Training Assigned" and "Training Attended" are both drop-down fields.

=IF([Training Assigned]@row = [Training Complete]@row, "Complete", "??????")

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    edited 12/07/23 Answer ✓
    Options

    @JCrawford

    Assuming the 3 columns you are describing are Multi select drop downs:

    Create a list of all your trainings (if you don't already have)

    In your [To Be Completed] column, add the below formula (replacer the bolded part with a reference to your list of training

    Formula: =IF([Training Assigned]@row = [Training Completed]@row, "Complete", JOIN(COLLECT({Reference Training List}, {Reference Training List}, AND(HAS([Training Assigned]@row, @cell), NOT(HAS([Training Complete]@row, @cell)))), CHAR(10)))

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    edited 12/07/23 Answer ✓
    Options

    @JCrawford

    Assuming the 3 columns you are describing are Multi select drop downs:

    Create a list of all your trainings (if you don't already have)

    In your [To Be Completed] column, add the below formula (replacer the bolded part with a reference to your list of training

    Formula: =IF([Training Assigned]@row = [Training Completed]@row, "Complete", JOIN(COLLECT({Reference Training List}, {Reference Training List}, AND(HAS([Training Assigned]@row, @cell), NOT(HAS([Training Complete]@row, @cell)))), CHAR(10)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!