How to generate the specified sequence of numbers based on incrementing portions of the sequence

[Rnkhch]

Hello,

Here is a rather complex sequence question Suppose I have the following partial LET function:

=LET(
   b, {2,1,3,4,2},
   c, 12,
   operation
)

The numbers in b indicate the count of numbers in each piece of the final sequence. The first piece starts with 1, and each piece after the first number is incremented by multiples of c starting at 1c for the second number of b, then 2c for the third number of b, and so on.

For the example above, the sequence starts with 1 and goes up for the indicated count in b, in this case 2, so the first piece of the sequence will be {1,2,...};
then, I want a "single" increment of c, i.e. 12, so the next element of the sequence would be 13, and since the second number of b is 1, this piece will have just one number, {...,13,...};
then, for the third number in b, I want a "double" increment of c, i.e. 24, so the third piece of the sequence will start at 25 and continue two more numbers up to 27, {...25,26,27,...};
then, for the fourth number in b, I want a "triple" increment of c, i.e. 36, and four numbers, {...,37,38,39,40,...};
and finally, for the fifth number in b, I want "quadruple" increment of c, i.e. 48, and two numbers, {...49,50}

So the final output should be a spill of {1,2,13,25,26,27,37,38,39,40,49,50}. Thus,


And here is another example:

=LET(
   b, {1,4,0,2},
   c, 8,
   operation
)

And for this I want a final output spill of {1,9,10,11,12,25,26}; so 0 in the input indicates that no number is needed for that part of the sequence.


And another example:

=LET(
   b, {0,3,1,4,0,0},
   c, 5,
   operation
)

And here the final output spill would be {6,7,8,11,16,17,18,19}.


I want to make a LAMBDA out of this , so I want it to work with any/variable counts of numbers in b.

I would appreciate any input

Thanks much!

 

[StephenCrump]

Here's one way:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	b	2	1	3	4	2
3	c	12
4
5	Output	1	2	13	25	26	27	37	38	39	40	49	50
6
Sheet1

Cell Formulas
Range	Formula
B2:F2	B2	={2,1,3,4,2}
B5:M5	B5	=LET(b,B2#,c,B3,m,SEQUENCE(MAX(B2#)),TOROW(IFS(m<=b,m+SEQUENCE(,COUNT(b),0,c)),2,1))
Dynamic array formulas.

 

[Rnkhch]

Thanks Stephen Amazing solution!

Also great to hear from you after a while.

 

[felixstraube]

Another option:

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	2	13	25	26	27	37	38	39	40	49	50
2	1	9	10	11	12	25	26
3	6	7	8	11	16	17	18	19
Sheet2

Cell Formulas
Range	Formula
A1:L1	A1	=LET( b,{2,1,3,4,2}, c,12, bs,SEQUENCE(,COUNT(b)), vs,SEQUENCE(MAX(b)), r,IF(vs<=b,vs+(bs-1)*c,""), TEXTSPLIT(TEXTJOIN(";",1,TOROW(r,,1)), ";") )
A2:G2	A2	=LET( b,{1,4,0,2}, c,8, bs,SEQUENCE(,COUNT(b)), vs,SEQUENCE(MAX(b)), r,IF(vs<=b,vs+(bs-1)*c,""), TEXTSPLIT(TEXTJOIN(";",1,TOROW(r,,1)), ";") )
A3:H3	A3	=LET( b, {0,3,1,4,0,0}, c, 5, bs,SEQUENCE(,COUNT(b)), vs,SEQUENCE(MAX(b)), r,IF(vs<=b,vs+(bs-1)*c,""), TEXTSPLIT(TEXTJOIN(";",1,TOROW(r,,1)), ";") )
Dynamic array formulas.

 

[Rnkhch]

Thank you Felix. Since the output of your code was text, I put the last line inside VALUE() and it works pretty well:

=LET(
   b,{2,1,3,4,2},
   c,12,
   bs,SEQUENCE(,COUNT(b)),
   vs,SEQUENCE(MAX(b)),
   r,IF(vs<=b,vs+(bs-1)*c,""),
   VALUE(TEXTSPLIT(TEXTJOIN(";",1,TOROW(r,,1)), ";"))
)

 

[felixstraube]

Actually looking at the formula from @StephenCrump it does basically the same thing and his is shorter with the IFS eliminating the not wanted values. (no like my convoluted solution with textjoin and textsplit)

​