# Compare two columns and input missing items into a third column

✭✭✭

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

• ✭✭✭✭✭✭
edited 12/07/23 Answer ✓

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

• ✭✭✭✭✭✭
edited 12/07/23 Answer ✓

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!