Is there a formula capable of building rows in google sheets?

Question

Not sure if this is possible or not, but essentially I need a formula that is capable of building rows in google-sheets.

My data is currently like this: Column A = Property Name, Column B = Routines. The Routines column has multiple values.

In another sheet I have mapped the Routine data values. For Example, where the value in the 'Routines' column A was previously 'Value 1 & Value 2' there is now Column B with 'Value 1' and Column C with 'Value 2'.

Now, In a new sheet I need my data to be in the following format Column A: Property Name, B: Mapped Routine, where each Routine Value is represented by a new row.
For example:

Row 1: Column A: Property A, Column B: Value 1
Row 2: Column A: Property A, Column B: Value 2

The only thing I can think to do is to X-Lookup the Value 1 in the New Routine format (i.e. Where 'Value 1 - Value 2 & Value 3' bring back 'Value 1') and then manually create new rows and bring back Value 2. But this isn't doable as there can be up to 5 New Routine Values for around 400 old Routines. Does anybody have any suggestions?

Answer 1

Depending how your routines are named and how they are joined in the strings there may be a few different ways to get the individual values for each property.

Formula based on your sample data "Value \d"

=LET(rng,A1:B, arr,FILTER(rng, LEN(INDEX(rng,,1))),  
   WRAPROWS(TOROW(MAP(INDEX(arr,,1), INDEX(arr,,2), LAMBDA(name, str,
     TOROW(MAP(INDEX(REGEXEXTRACT(str, REGEXREPLACE(str, "(Value \d)", "($1)"))),
       LAMBDA(routine, {name; routine})),,1))),1),2))

Formula that uses a range of lookup values

Assuming you have a list of routine names in Column D, the regular expression could instead be

"("& TEXTJOIN("|", 1, D:D) &")"

So the formula would then be

=LET(rng,A:B, arr,FILTER(rng, LEN(INDEX(rng,,1))),  
   WRAPROWS(TOROW(MAP(INDEX(arr,,1), INDEX(arr,,2), LAMBDA(name, str, 
     TOROW(MAP(INDEX(REGEXEXTRACT(str, 
       REGEXREPLACE(str, "("& TEXTJOIN("|", 1, D:D) &")", "($1)"))),
          LAMBDA(routine, {name; routine})),,1))),1),2))

FILTER  INDEX  LAMBDA  LEN  LET  MAP  REGEXEXTRACT  REGEXREPLACE  TEXTJOIN  TOROW  WRAPROWS