Creating a list from permutations of options

I’m looking for ways to concatenate (I think) a row of two different columns to make a set of SKUs for our retail situation. The catch is that one of the columns often carries a couple different options in it, and I’m having trouble getting a single list that includes all the combinations of it.

Our SKUs are built from an abbreviation of what the Doodad we’re building is and the options that it takes on. For example we have both Orange and Pink Gizmos that have SKUs respectively of GizOrange and GizPink while Widgets come only in orange and only have WidOrange as a SKU. I’m looking for a way to create a list of all the SKUs automatically, ideally one that would be available for me to draw upon later when making invoices etc with a lookup column.

Any help that could be offered would be huge! Thanks so much.

There was a hackathon about a similar problem:

There are multiple ways to approach this:

  • With a button that would generate the full product of two lists into a separate table
  • With a column formula (updated live) + pre-made sufficient number of rows
  • With a column formula and automation that would add/delete rows from the product table (although with some delay)