Google Sheet split cell to rows - data manipulation

Question

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

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:

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

Answer 1

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.