Automatically create rows in a table for each unique value found in another table (multiple columns and rows)

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:

1 Like

Hello @Brenton_M!

Could you update your share permissions so we can access your doc please?


Share a document publicly

@Saul_Garcia whoops! It should be viewable now - thanks for the heads up.