compare multi-contacts to a text string

joe86

Hi everyone,

I am building a schedule for my team that will confirm if a combination of assigned staff 'assigned to' are trained to do a specific task 'task combos'.

the 'staff trained in this task' is a text column that is using JOIN(COLLECT) to combine contacts who are trained in a task, these are in different columns not seen below.

Because i cant join contacts in a single cell my idea was to convert 'assigned to' into a text string and compare that to the 'staff trained…" and use the checkbox to confirm if those are indeed trained staff. in the checkbox column my current 'not working' formula is

=IF(CONTAINS([text of 'assigned to']@row, [staff trained in this task]@row), 1, 0)

My problem seems to be the commas when i convert the contacts into text, i cant seem to get rid of that, however i feel there must be a way to ignore the comma and just search the names?

thanks in advance

Joe