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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!