Compare two columns and input missing items into a third column

JCrawford

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

Leibel S

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

Leibel S

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