Hi! I have a sorta-tricky problem that I can’t figure out: I want to take the contents of multiple cells (across multiple rows and columns) in one table (“Movie Table”), parse unique values, and automatically add/remove rows from another table (“People”) for each unique item. I think it’s a two-part problem:
Problem 1. Create a list (is a list the best output for this use case?) containing each unique value found across multiple columns and rows.
Right now, I’m doing this via the formula Split(ListCombine([Movie Table].Director,[Movie Table].Producers, [Movie Table].Starring,[Movie Table].[Written by]).Unique(),", "). For now, this allows me to create a select-list in the “People” table that shows only unique values.
Problem 2: automatically add/remove rows to a table based on the contents of unique values contained within multiple cells in a different table.
Now that I have my list of unique values, I want the “People” table to automatically add/remove rows whenever this list of unique values changes. For example, if I add actors to the movie Okja in the “Movie Table” table, I want a new row in the “People” table to appear for each actor. If I remove the name of a producer for a different movie (and if that person isn’t listed anywhere else), I want their row in the “People” table to be removed. Removing rows is more of a nice-to-have, though.
Here’s a copy of my doc:
