JOIN(DISTINCT(COLLECT + JOIN(DISTINCT(COLLECT but how to remove duplicates due to using two formulas

Hi all,

First post but I've been reading (and making use of!) many of the queries & answers for a while. Stuck on this one!

I have two JOIN(DISTINCT(COLLECT formulas that I am adding together, but sometimes the same person's name will appear twice due to being pulled through by both formulas. How do I prevent this type of duplication?

=JOIN(DISTINCT(COLLECT(Name:Name, Region:Region, "Asia", Prevention:Prevention, "", Paperwork:Paperwork, "No")), CHAR(10)) + CHAR(10) + JOIN(DISTINCT(COLLECT(Name:Name, Region:Region, "Asia", Prevention:Prevention, "", Services:Services, "No")), CHAR(10))

"PersonA" is returned because their Name is in rows that include all the following at once: "Asia" in the Region column, and [blank] in the Prevention column, and "No" in the Paperwork column.

"PersonB" is returned because their Name is in rows that include all the following at once: "Asia" in the Region column, and [blank] in the Prevention column, and "No" in the Paperwork column AND their Name is in rows that include all the following at once: "Asia" in the Region column, and [blank] in the Prevention column, and "No" in the Services column.

"PersonC" is returned because their Name is in rows that include all the following at once: "Asia" in the Region column, and [blank] in the Prevention column, and "No" in the Services column.

I want it to return as follows:

PersonA

PersonB

PersonC

but instead it returns as follows:

PersonA

PersonB

PersonC

PersonB

Any suggestions would be most welcome!

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

All comments

Paul Newcome

If you drop it into a multi-select dropdown column, it should clear that up for you.

AEForrest

https://community.smartsheet.com/discussion/comment/377140#Comment_377140

Hi Paul, thanks for your response. The formula as a whole is sat in the Sheet Summary, and the individuals names also can change quite frequently so it wouldn't work for me to have them as dropdowns on the Sheet.

Or do you mean something else entirely? :)

Paul Newcome

You can drop the formula into a hidden multi-select dropdown column to clean up the list, then reference that cell in the Sheet Summary field.

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.