Splitting Cell into Multiple row using array

vector 0
Viewed 36

I am trying to split the range of data like this :-

enter image description here

At first I was using this :-

=ARRAYFORMULA(IF(ROW($B$2:$B)>=(SUM($A$2:$A)+ROW()),"",IFERROR(SPLIT(LOOKUP(ROW($B$2:$B)-ROW(),IF(ROW($B$2:$B)>=(SUM($A$2:$A)+ROW()),"",SUMIF(ROW($A$2:$A),"<"&ROW($A$2:$A),$A$2:$A)),A2:A),""),"")))

But this didn't worked, I got this from another stack answer, I know this is not right :(, I can't reach anywhere with this

This is the sheet URL :- https://docs.google.com/spreadsheets/d/1oB1i0IAGoCVN0ynKBXDQcmEd3c_mpOdmWVFo7Gz-vyk/edit#gid=0

Thanks

google-sheets
2 Answers

Formula for you

=TRANSPOSE(SPLIT(JOIN(CHAR(10),A2:A7),CHAR(10),1,0))

enter image description here

Function References

Theza0

REDUCE the array by SPLITting by CHAR(10):

data Intended Result
1 1
1
2
3		 1
2
3 
=REDUCE("Intended Result",A2:A5,LAMBDA(a,c,{a;IFERROR(TRANSPOSE(SPLIT(c,CHAR(10))))}))
TheMaster0
Related