How to check if all people in a column are in another (mirrored) people column

Platform discussions Special workflows & use cases
, ,
1

In other words…How do I check if every word in a cell is in another cell, regardless of word order?

Hi, have a complex logistic workflow for a cycling team.
There is a a board with each race as an item and all the riders/staff that will take part to a race in multiple people column. I merged tohse columns toghether with a CONCATENATE formula.

I use another board to manage flights tickets and other travel types. I have a people column {Traveler} for every person that will travel with that flight or vehicle.

I want to be sure that ALL the users in that people column (more than one) are in the people column on the other board (that I mirrored here).

By now i got it with this expression: SEARCH({Traveler#Names},{Attendees})
But it doesn’t work when there are more than one travelers.

Any suggestions?

2

Hi Dennis,

As far as I am aware, you can’t sort the persons in a people column (either in the settings of the column or by using a function of the formula column). If it were possible, then you could do IF({Traveler#Names}={Attendees},“Match”,“No Match”). Without it, comparing"Person1,Person2" with “Person2,Person1” will not give you a match.

The Advanced Formula Booster (which is a 3rd party app) does sort the persons automatically, so it is really easy to compare 2 people columns.

3

Thanks Gilles.
I downloaded the App but the formula you mentioned doesn’t work.
Could you explain me how to compare those 2 people column?

4

Certainly! It seems the provided formula may not be suitable for your situation. Please provide more details or context, and I’ll be happy to help you craft a formula for comparing the two people columns.Fm Whatsapp

5

Hi Dennis,

In the App Center, in the left pane at the bottom, click Support then click the Go to Ticket Center button and open a ticket (you’ll be prompted to select the formula you created) and I’ll be able to help you while looking at your syntax.

6

Use IF(AND(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE({Traveler#Names}, " ", REPT(" ", 100)), (ROW(INDIRECT("1:" & LEN({Traveler#Names}) - LEN(SUBSTITUTE({Traveler#Names}, " ", "")))) - 1) * 100 + 1, 100))), {Attendees}))), "Match", "No Match") to check if all words exist, regardless of order.