Lambda and custom sequence with interference of string

Phantom1

New Member
Joined
Sep 26, 2018
Messages
22
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
1729102670996.png


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...
1729103210939.png


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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Here's one way you could do it, with a recursive Lambda.

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
15
260
34
423
512
6@@59@131721@@2529@333741@@4549@5357
7
84
960
103
11234
12321
13@/47@@@10131619222528/3134@@@37404346495255/58
Sheet1
Cell Formulas
RangeFormula
B4:C4B4={2,3}
B5:C5B5={1,2}
B6:C6B6={"@","@"}
F6:Z6,F13:AJ13F6=CustSeq(B1,B2,B3,B4#,B5#,B6#,1)
B11:D11B11={2,3,4}
B12:D12B12={3,2,1}
B13:D13B13={"@","","/"}
Dynamic array formulas.
Lambda Functions
NameFormula
CustSeq=LAMBDA(startnum,maxend,step,breaks,rep,str,col,LET(c,1+MOD(col-1,COLUMNS(breaks)),seq,SEQUENCE(,INDEX(breaks,c),startnum,step),IF(MAX(seq)<maxend-step,HSTACK(seq,IF(SEQUENCE(,INDEX(rep,c)),INDEX(str,c)),CustSeq(startnum+step*INDEX(breaks,c),maxend,step,breaks,rep,str,col+1)),FILTER(seq,seq<=maxend))))
 
Upvote 0
Solution
Excellent solution. Exactly what i was looking for.
Thanks a lot.
Cheers!
 
Upvote 0

Forum statistics

Threads
1,222,833
Messages
6,168,523
Members
452,194
Latest member
Lowie27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top