Dynamic array function to split value into rows

Question

I want to try an array formula that spills

I tried this =HSTACK(J18,TOCOL(TEXTSPLIT(K18," "))) but number in A column doesn't repeat

Sample data

898990	Cat Mat
898767	Nam Bam
767676	Gam Jam

Either way is okay

Answer 1

Hi,

I would solve this in the Query Editor. However, if you wish to use a formula, then enter this formula in cell D2

=LET(spt,DROP(REDUCE(" ",B2:B4,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ")))),1),HSTACK(TOCOL(IF(spt<>"",A2:A4),2),TOCOL(spt,2)))

Hope this helps.

Answer 2

This one?

=HSTACK(TEXTSPLIT(TEXTJOIN(" ",,TRIM(REPT(A1:A3&" ",2))),," "),TEXTSPLIT(TEXTJOIN(" ",,B1:B3),," "))

Answer 3

Yes, PQ would split into rows. But I want to make use of dynamic arrays in place for quick analysis also to avoid swithing to query editor for simple things. If its automated task then I will use PQ. We even have pandas in Excel for quick analysis.