Column "all but"

For a school play doc, I have a column with students’ names and another with the roles they are assigned.

In another table, I have listed the scenes and lookup columns that show which characters and corresponding actors are in that scene. I want the next column to show me who is NOT in that scene – show me all names except for the ones in the previous column. How would I do that?

Something like

Students.Filter(CurrentValue.In(thisRow.[Students in this scene]).Not())
1 Like

Thanks so much! That worked. I have another small issue that maybe you can also help with. Since each student plays multiple roles, each name is showing up 5 or 6 times. Any idea how to filter it even more so each student only comes up once?

You can append .Unique() onto your non-unique list to get a list filtered for only unique items.

However I’d rather look into how it happened in the first place that you were having duplicates, and fix it there.

1 Like