google sheets, splitting and stacking a paragraph-CodePudding

I have a 3 row by 2 column table

1Q18 hello. testing row one.
2Q18 There are about 7.5b people. That's alot.
3Q18 Last sentence. To be stacking.

I want to split each sentence then have a quarter label with it, out would be

1Q18 hello
1Q18 testing row one
2Q18 There are about 7.5b people
2Q18 That's alot
3Q18 Last sentence
3Q18 To be stacking

I can get one line to work with: =TRANSPOSE({split(rept(A1&" ",counta(split(B1,".")))," ");split(B1,".")}) which would give me:

1Q18 hello
1Q18 testing row one

I need a formula that will let me go down 100 rows, so I can't manually repeat the formula 3 times and use {} with ;

I've also tried using the =map(A1:A,B2:B,LAMBDA(x,y,TRANSPOSE({split(rept(x&" ",counta(split(y,".")))," ");split(y,".")}))) but get a

Error Result should be a single column.

CodePudding user response：

try:

=INDEX(QUERY(SPLIT(FLATTEN(LAMBDA(x, IF(x="",,A1:A&""&x))
 (SPLIT(B1:B&" ", ". ", ))), ""), "where Col2 is not null", ))

CodePudding user response：

Try below formula-

=QUERY(REDUCE(,B1:B3,LAMBDA(a,x,{a;TRANSPOSE(INDEX(INDEX(A1:A,ROW(x)) & " " & SPLIT(SUBSTITUTE(x,". ",".|"),"|")))})),"offset 1",0)