Google Sheets Array Formula with Multiple Sequences of Varying Lengths, Starting Numbers, and Suffixes?

Question

I've got a unique situation where I'm trying to determine the numbers present in multiple, variable, sequences. Assume each column and row start at A1.

StartNum	NumItems	Suffix	Sequence Formula	E	F	G	H
1	2	e	1e	2e
20	5	d	20d	21d	22d	23d	24d
33	4	a	33a	34a	35a	36a

I can achieve the results in Columns D and beyond with the following formulas into D1, D2, and D3:

=ARRAYFORMULA(SEQUENCE(1,B1,A1,1)&C1)
=ARRAYFORMULA(SEQUENCE(1,B2,A2,1)&C2)
=ARRAYFORMULA(SEQUENCE(1,B3,A3,1)&C3)

As I add additional rows, however, I have to manually copy down the formula in D1/D2. I'd love to have a single formula in D1 that generates the entire 2D array of values, with the ultimate goal of eventually concatenating everything into a single string that is pipe-separated using something like JOIN("|",{some-array-formula}) to achieve the final result of "1e|2e|20d|21d|22d|23d|24d|33a|34a|35a|36a".

Any thoughts on how to achieve this? I've tried the following formula:

=ARRAYFORMULA(ARRAYFORMULA(SEQUENCE(1,B1:B,A1:A,1)&C1:C))

but that didn't accomplish what I was looking for in terms of building the multi-dimensional array. Instead it just gives me a single-column array with just the values in Column D.

Answer 1

You may try below formula-

=LET(lr,COUNTA(A2:A),
a,A2:INDEX(A2:A,lr),
b,B2:INDEX(B2:B,lr),
c,C2:INDEX(C2:C,lr),
INDEX(MAP(a,b,c,LAMBDA(x,y,z,SEQUENCE(1,y,x)&z))))

• Here A2:INDEX(A2:A,COUNTA(A2:A)) will return a array of values as well cell reference from A2 to last non empty cell in column A (Assume you do not have any blank rows inside data). Same for Col_B and Col_C. If you have blank row, then you have to use different approach. See this post by @TheMaster

• Then LAMBDA() will apply SEQUENCE() function for each cell of A, B, C column.