Help with Join Collect formula

gwson

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

Paul Newcome

My suggestion would be a flag type column (on the source sheet) with a formula to flag if it has not been mapped anywhere.

=IF(COUNTIFS(Subcompetency:Subcompetency, @cell = Subcompetency@row, Gap:Gap, @cell = "Mapped") = 0, 1)

Then on the consolidation sheet you would use the JOIN/DISTINCT/COLLECT combo, but the only range (other than the one to join) would be this new flag column and the criteria would be that it is flagged.

You could also create a report based on this column being flagged if you just wanted a list of those that aren't mapped at all.

Paul Newcome

My suggestion would be a flag type column (on the source sheet) with a formula to flag if it has not been mapped anywhere.

=IF(COUNTIFS(Subcompetency:Subcompetency, @cell = Subcompetency@row, Gap:Gap, @cell = "Mapped") = 0, 1)

Then on the consolidation sheet you would use the JOIN/DISTINCT/COLLECT combo, but the only range (other than the one to join) would be this new flag column and the criteria would be that it is flagged.

You could also create a report based on this column being flagged if you just wanted a list of those that aren't mapped at all.

gwson

Yes, that did the trick! You're the best, thank you as always!

Paul Newcome

Happy to help. 👍️