How to create multiple sequences of varying lengths with arrayformula-CodePudding

Let's say I have a list of 3 values in a column A. The amount of values is arbitrary, so there can be more. So for example:

 |A|B
- - -
1|4|
2|2|
3|3|

In the column B, I would like to get a sequence, which would include increasing sequences with the lengths of the values given in column A. So the above example would be:

The expected sequences:
4 = 1,2,3,4
2 = 1,2
3 = 1,2,3

The expected table:

  A|B
--- -
1|4|1
2|2|2
3|3|3
4| |4
5| |1
6| |2
7| |1
8| |2
9| |3

I am currently achieving this by having the following function in B1: ={SEQUENCE(A1);SEQUENCE(A2);SEQUENCE(A3)} but as you can see, if I were to add another value to the column A, I would need to modify the said function. How can I achieve this dynamically?

CodePudding user response：

Another approach (for google sheets). In B1

=Arrayformula(transpose(split(textjoin("~", 1, if(column(1:1)<=A1:A, column(1:1),)), "~")))

CodePudding user response：

say your data starts at A2, then use the following formula:

=ARRAYFORMULA( transpose( split( join("\", LEFT(join( "\", COLUMN(offset($A$1,,,,max($A2:$A))) ), offset($A$2,,,COUNT($A2:$A))*2-1) ) ,"\") ) )

in cell B2. Mind that if you have numbers >= 10 then special care must be taken with text( ... ,"00") and *2 --> *3 and so on, accordingly if you expect for >=100...