Rollup of contacts into a single cell as a single select or comma sep string?

I have a project plan with 100 tasks.

Each task can have one or more assignees in the Assigned To column.

I want to have a rollup of all possible assignees into a single cell for project filtering (as opposed to task filtering).

I understand i can not do a formula with multiple contacts. I am OK to translate this into a multi-select cell or a text cell with comma separated values for this rollup cell itself.

The problem with my Distinct(Collect formula is that its treating each cell as the unique string as opposed to treating the individual names in each cell as the unique strings. Is there a formula that can analyze the collected strings and remove duplicates? This way if "NAME-A" appears in 6 different tasks with other combinations of names, the end rollup would still only see that as a single "NAME-A".

=JOIN(DISTINCT(COLLECT([Assigned To]43:[Assigned To]150, [P/C]43:[P/C]150, ="CHILD")), " , ")

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @Matt Stewart

Try writing the formula into a Multi-Select column as well! This column type should be able to identify the individual selections as unique, even when they're associated with another cell.

You would need to have the values separated by CHAR(10) instead of a comma in this case.

Try:

=JOIN(DISTINCT(COLLECT([Assigned To]43:[Assigned To]150, [P/C]43:[P/C]150, ="CHILD")), CHAR(10))

Let me know if this works for you.

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Matt Stewart

Try writing the formula into a Multi-Select column as well! This column type should be able to identify the individual selections as unique, even when they're associated with another cell.

You would need to have the values separated by CHAR(10) instead of a comma in this case.

Try:

=JOIN(DISTINCT(COLLECT([Assigned To]43:[Assigned To]150, [P/C]43:[P/C]150, ="CHILD")), CHAR(10))

Let me know if this works for you.

Cheers,

Genevieve

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.