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!
Answers
-
If you drop it into a multi-select dropdown column, it should clear that up for you.
-
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? :)
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!