How would I parse out a contact field with multiple contacts

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.

Find more posts tagged with

Contacts

Accepted answers

All comments

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.

Screenshot 2023-07-12 at 11.56.56 AM.png

Patrick1234

https://community.smartsheet.com/discussion/comment/384870#Comment_384870

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.

cthrasher97141

INDEX/ATCH is also an option it looks left and right in a row

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.