I need to create a custom sequence of numbers but interfere with custom strings with rules.
To be specific let's say i want a sequence of numbers starting from 5 and go up until the biggest number that is smaller than 60 with step of 4.
The task is that we want to enter one @ symbol after the first two numbers, then two @ symbols after the next three numbers and continue with the same pattern.
I have created the following lambda and added as CustSeq in Name Manager which works for the given example.
=LAMBDA(startnum,maxend,step,breaks,rep,str,
LET(a,SEQUENCE(1,CEILING((maxend-startnum)/step,1),startnum,step),
b,SEQUENCE(1,COLUMNS(a)),
c,MAP(b,LAMBDA(i,IF(MOD(i,BYROW(breaks,SUM))=INDEX(breaks,1),REPT(str,INDEX(rep,1)),IF(MOD(i,BYROW(breaks,SUM))=0,REPT(str,INDEX(rep,2)),"")))),
d,VSTACK(a,c),
out,FILTER(TOROW(d,1,TRUE),TOROW(d,1,TRUE)<>"",),out))
Example of input and output.
The problem with this is that it is fixed to specific row arrays of exact two elements.
What if i wanted
=CustSeq(5,60,3,{1,2,4},{1,2,1},"@") ?
Something i just notice is that we do not want the character or string when repeating to be joined as in cell M3 of the example. We want it to take its own cell.
And what about blanks?
If we want the result to be...
I am sure there is a better ,more dynamic and efficient way.
Did anyone make something like this?
Do not seek for VBA. Just a custom Lambda function to be added in Name Manager.
Thanks in advance.
To be specific let's say i want a sequence of numbers starting from 5 and go up until the biggest number that is smaller than 60 with step of 4.
The task is that we want to enter one @ symbol after the first two numbers, then two @ symbols after the next three numbers and continue with the same pattern.
I have created the following lambda and added as CustSeq in Name Manager which works for the given example.
=LAMBDA(startnum,maxend,step,breaks,rep,str,
LET(a,SEQUENCE(1,CEILING((maxend-startnum)/step,1),startnum,step),
b,SEQUENCE(1,COLUMNS(a)),
c,MAP(b,LAMBDA(i,IF(MOD(i,BYROW(breaks,SUM))=INDEX(breaks,1),REPT(str,INDEX(rep,1)),IF(MOD(i,BYROW(breaks,SUM))=0,REPT(str,INDEX(rep,2)),"")))),
d,VSTACK(a,c),
out,FILTER(TOROW(d,1,TRUE),TOROW(d,1,TRUE)<>"",),out))
Example of input and output.
The problem with this is that it is fixed to specific row arrays of exact two elements.
What if i wanted
=CustSeq(5,60,3,{1,2,4},{1,2,1},"@") ?
Something i just notice is that we do not want the character or string when repeating to be joined as in cell M3 of the example. We want it to take its own cell.
And what about blanks?
If we want the result to be...
I am sure there is a better ,more dynamic and efficient way.
Did anyone make something like this?
Do not seek for VBA. Just a custom Lambda function to be added in Name Manager.
Thanks in advance.