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

Georgie

Hi @joe86,

Rather than trying to remove the comma and space from the “text of ‘assigned to’” column, how about adding the comma and space as a delimiter in the JOIN(COLLECT) formula in the “staff trained in this task” column? For example, your formula would look similar to:

• =JOIN(COLLECT([assigned to]:[assigned to], test:test, "yes"), ", ")

Check out this help article for more information on using JOIN and COLLECT together:

• Formula combinations for cross sheet references

Does that work for you?

Georgie