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

orenwick

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

Leibel S

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

Leibel S

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

orenwick

Awesome, it worked! Thank you so much!

orenwick

@Leibel Shuchat the CHAR (10) doesn't seem to be working. The trainings aren't line breaking. I tried moving it around in the equation but couldn't get it to not give me an error. Thank you again for your help!

Leibel S

What is your {Trainings} Reference?

orenwick

It is just a new sheet with all the names of the trainings in a single column and that is all that is on it. I named the sheet trainings and the column trainings.

Leibel S

Can you send a snapshot of the cross sheet reference, and what is happening with the formula?

Is the column a multi select column?

orenwick

Everything is generally working that last column just doesn't stack the trainings.

Leibel S

You can either change it to a multi select column (Similar to the other ones), or you need to 'Wrap Text' for it to show as separate lines.

orenwick

Wrap text did it! Thank you so much for your help!

Joelle Lillich

I am trying to do a similar task and this thread has been most helpful. However, I'm not understanding the reason for creating a list of Trainings in a separate sheet and how that is being used in the formula. Can anyone clarify that, or am I better off creating a new post with my specific scenario/question?

Leibel S

@Joelle Lillich

We need a way to go through each possible item and see if this one should be included here or not. So we make a separate list for that