Help with Join Collect formula

gwson ✭✭✭✭✭
edited 01/22/24 in Formulas and Functions

I have a long list of subcompetencies, that are mapped to course objectives across courses.

What I would like like a formula that displays subcompetencies where objectives are equal to "NA", but if the subcompetency is already mapped in a different course to a course objective, then do not display. In other words, i need a way to find which competencies have not been mapped at all across all courses.

In screenshot below, 1.2a is a gap in 3118, but is mapped in 3119 and 3120, so we're good, and i do not wish to display it. I tried using a helper column as you see and referencing that but i still get 1.2a to show for some reason.

=JOIN(DISTINCT(COLLECT({subcomp}, {gap}, "GAP", {gap}, <>"Mapped")))

@Paul Newcome


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!