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

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?

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))
``````

• You are a godsend! Thank you so much. Commented yesterday
• Glad I could help and welcome to the community! If you found the answer useful, rather than commenting you can accept and/or upvote it see What should I do when someone answers my question? Commented yesterday