1

I have a sheet, where there is a list of times in Col. C.

enter image description here

What I want is for it to be split into different rows & also the relevant order number & name to be copied with the rows. Resulting look:

enter image description here

I have about a 300 rows of orders to do this way. Right now I'm doing it manually and its taking ages. And what I posted here is only a part of the process.

Here is the sample spreadsheet: https://docs.google.com/spreadsheets/d/1VENgvRAdBaAC28lpJ5a2QvD5KtnD-S3dx5rUELzvGCM/edit?usp=sharing

1 Answer 1

2

See Test file Sheet "What I want".

Following formula in I2 returns split list of items and names repeated

=ARRAYFORMULA(
 QUERY(
  SPLIT(
   FLATTEN(
    FILTER(Original!B2:B,Original!B2:B<>"")&"|"&
     SPLIT(
      IFERROR(
       REGEXREPLACE(
        FILTER(Original!C2:C,Original!C2:C<>""),"\n","&"),FILTER(Original!C2:C,Original!C2:C<>"")),"&")),
   "|"),
 "SELECT *
 WHERE Col2 <> ''"))

Main idea is to use FLATTEN() formula to join two lists of NAME and LIST.

For NAME split we change indents to "&" using REGEXREPLACE() and then separate them with SPLIT() and TRANSPOSE() formulas.

QUERY() is there just to filter blanks.

In H2 we use SEQUENCE() to create IDs for all entries.

In L2

=ARRAYFORMULA(VALUE(REGEXEXTRACT(FILTER($J$2:$J,$J$2:$J<>""),"\d+")))

REGEXEXTRACT() is used to get quantity of items from LIST.

In M2 also added VLOOKUP of ORDER NO.

4
  • thank you so much this worked! :) :)
    – Genny
    Mar 26, 2021 at 1:56
  • can we adjust the formula to multiply the order number too pls, instead of adding a separate number for the ID?
    – Genny
    Apr 2, 2021 at 0:50
  • 1
    Sure, added to the sheet. this part FILTER(Original!A2:A,Original!A2:A<>"")&"|"&
    – Oleg_S
    Apr 2, 2021 at 6:15
  • Thank you so much Oleg! :) :) :)
    – Genny
    Apr 2, 2021 at 7:47

Your Answer

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

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