How would I parse out a contact field with multiple contacts

Patrick1234 ✭✭
edited 07/12/23 in Formulas and Functions

We have multiple projects, each with their own project plan sheet. On each sheet we have the usual project plan columns, Start Date, End Date, Duration etc. We also have an Assignee (resource) column that is a multiple contact type. This works well on the project plan.

However ...

We have the same resource on multiple projects and I would like to be able to identify if any resource that is double booked for the same period.

I need to be able to parse out each contact from the multiple column with the date range. I do not mind if I extract the data into another column or sheet or report or whatever.

Unfortunately due to the possible unknown amount of contacts I can not have a column per contact on the project sheet.

Any help would be greatly appreciated.



  • cbenjamin

    You could use a VLOOKUP. Lookup the contact's name(search value) referencing the project plan sheet as the lookup table. The column num would be the date in the project plan sheet.

    Have a row for each individual, and a column for each project plan sheet. This will put all the dates for each individual in one row.

    Use cell formatting to highlight duplicates in each row. The highlighted cells will show you the dates they are double booked. Something to this effect, it could probably be sussed out with a formula that would be more effective too, but hopefully you get the idea.

  • Patrick1234

    Thank you for your idea. I may use it to solve a different problem.

    Unfortunately it will not help me here as it requires the user to enter resources in multiple locations. The users are going to enter the resource once on the project sheet, the extraction and checking needs to happen in the background.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!