Search code examples
google-sheetsgoogle-sheets-formulaarray-formulas

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

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.

Solution

  • 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.

    enter image description here