0

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.

Screenshot 1

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'.

Screenshot 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

Screenshot 3

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?

1

1 Answer 1

0

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

Screenshot showing formula and results in Google Sheets

FILTER  INDEX  LAMBDA  LEN  LET  MAP  REGEXEXTRACT  REGEXREPLACE  TEXTJOIN  TOROW  WRAPROWS

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.