VSTACK for dynamic sequences PRIOR to their creation*

Question

I am trying to find the VSTACK for any number of sequences that can be formulated as:

sequence(to_ - from_ + 1) + (from_ - 1)

where column headings to_ and from_ represent the start and end points of respective sequences. For instance, consider the following table of start and end points (from_, to_ resp.):

Example input/output and illustrating possible (albeit sub-optimal in relation to desired/robust soln for Q in hand)

Input

Given this, the desired output should vertically stack 32,46,95, and 45 (comprising sequences with one element each) on top of the (vertical) sequence ranging 48,49,..,57, and likewise for 65,66,..,90.

Output

This would give an output as follows:

The manual way would look something like this:

=VSTACK(SEQUENCE(f2-e2+1)+e2-1, SEQUENCE(f3-e3+1)+e3-1 , … , SEQUENCE(f7-e7+1)+e7-1))

---

Notes:

Note: this is specific to Office 365 compatible versions of Excel; no interest in helper functions, or soln involving VB, Python in Excel/Advanced Formula Editor add-ins etc.

For reference, I have reviewed questions marked as 'similar' - these did not seem to be readily adaptable to the question in hand:

I have a plausible solution but it lacks parsimony, hoping someone is aware of a more tractable method (still fixing /tweaking so can share for possible refinement as req.).

---

Related (but adequately different) Qs reviewed

1. This Q (top soln goes to hometown, courtesy @JvdV as of present posting)

• Cells already comprise concatenated lists with with suitable delimiter
• Direct application infeasible given when a series of sequences should necessitate the ByRow/LAMDBA duet or similar - further complicating matters
• Other solns utilized VB or were 'static' (i.e. similar to 'long method' above)

2. Here is another variation - courtesy @MrExcel MVP

• clearly the format/set up is completely different with disjoint lists that appear to lend themselves to the 'long/manual' method - i.e. adding another 'dynamic list' requires manually updating the function to reference the appropriate 'added range/dynamic list)

3. SuperUser here appears to have what I'm looking for with toCol for range of arrays, yet does not seem to work / after some adaptation, Q in hand

4. Chat-GPT didn't solve either, for interest's sake it did propose reduce function which hadn't occurred to me.

Conclusion: popularized techniques appear well-suited to cases where arrays do not first need to be created, i.e. 1 & 3 have these as a given already, in my case, these need to be created.

---

Use Case

Various advantages RE: data cleaning/analysis and preparation; e.g. in my case I want to reference all Unicode values that produce undesirable characters using Unichar; for the purpose of assimilating the necessary data for analysis.

---

Answer 1

Perhaps something along the lines of using TOCOL() function while doing a BOOLEAN LOGIC comparison:

---

=LET(
     _Data, E2:F7,
     _To, TAKE(_Data,,-1),
     _NOfRows, SEQUENCE(,MAX(_To)),
     TOCOL(IFS((_NOfRows>=TAKE(_Data,,1))*(_NOfRows<=_To),_NOfRows),3))

---

• _Data --> Variable to store the dataset,
• _To --> Variable is the end of each start data,
• NOfRows --> Variable to get the max value from the range,
• Lastly, doing a Boolean Logic between the _NOfRows with the _From & _To if the criteria returns TRUE, it will give _NOfRows and if not returns #N/A which is parsed using TOCOL();s function 2nd parameter as well using to stack vertically.

---

Bit shorter version of the above:

=LET(
     α, F2:F7,
     δ, SEQUENCE(,MAX(α)),
     TOCOL(δ/((δ>=E2:E7)*(δ<=α)),2))

---

ADDENDUM: One doesn't needs a LAMBDA() recursive helper function for the above question, as well, if someone makes such basic typos even when entering manually then could use the following, however one shouldn't do such irregular/random typos, to increase # of functions(too many functions to play makes excel to run slow, slower, slowest.....).

=LET(
     _Data, IF(E2:E7>F2:F7,F2:F7&"|"&E2:E7,E2:E7&"|"&F2:F7),
     _Start, --TEXTBEFORE(_Data,"|"),
     _End, --TEXTAFTER(_Data,"|"),
     _NOfRows, SEQUENCE(,MAX(_End)),
     TOCOL(_NOfRows/((_NOfRows>=_Start)*(_NOfRows<=_End)),2))

---

Using LAMBDA() helper function called MAKEARRAY()

=LET(
     _Data, IF(E2:E7>F2:F7,F2:F7&"|"&E2:E7,E2:E7&"|"&F2:F7),
     _Start, --TEXTBEFORE(_Data,"|"),
     _End, --TEXTAFTER(_Data,"|"),
     _NOfRows, _End-_Start+1,
     TOCOL(MAKEARRAY(ROWS(_NOfRows),SUM(_NOfRows),LAMBDA(r,c,IF(c<=INDEX(_NOfRows,r),INDEX(_Start,r)+c-1,p))),2))

---

---

Using MAP()

=LET(
     _Data, IF(E2:E7>F2:F7,F2:F7&"|"&E2:E7,E2:E7&"|"&F2:F7),
     _Start, --TEXTBEFORE(_Data,"|"),
     _End, --TEXTAFTER(_Data,"|"),
     _Max, MAX(_End-_Start+1),
     --TOCOL(TEXTSPLIT(TEXTAFTER("|"&MAP(_Start,_End,LAMBDA(x,y,TEXTJOIN("|",,SEQUENCE(y-x+1,,x)))),"|",SEQUENCE(,_Max)),"|"),2))

---

Answer 2

Stack Sequences Defined By Two Columns

=LET(data,E2:F6,
    DROP(REDUCE("",SEQUENCE(ROWS(data)),LAMBDA(rr,r,LET(
        start,INDEX(data,r,1),
        end,INDEX(data,r,2),
        s,end-start,
        step,SIGN(s),
        VSTACK(rr,SEQUENCE(s*step+1,,start,step))))),1))

Answer 3

I know that the question is for 365 but want to show the restraints of array functions. This formula seems a very simple and clear approach, but works only on GOOGLESHEET.

With BYROW and also with TOCOL function but another SEQUENCE technic.

This formula with excel-online returns #CALC! error because of nested arrays are not supported.

=TOCOL(BYROW(E2:F7,LAMBDA(inp,sequence(1,index(inp,1,2)-index(inp,1,1)+1,index(inp,1,1)))),3)

The result is the same as in the posts.

Answer 4

I received some excellent answers that I would not have thought about/known to improve the solution I mentioned that I had from the outset - given mine felt it too 'unwieldy'; i.e. far from elegant/prosaic - here it is, fyi (not sure how it managed to 'work' but had to go around a bit to get to the something that 'worked'):

=LET(x_,BYROW(AI2#,LAMBDA(a_,LET(a0_,INDEX(a_,0,1),a1_,INDEX(a_,0,2),b_,TEXTJOIN("¬",1,SEQUENCE(a1_-a0_+1)+(a0_-1)),c_,TEXTJOIN("¬",TRANSPOSE(b_),1),b_))),y_,TEXTJOIN("¬",1,x_),z_,TEXTSPLIT(y_,,"¬",1),z_)

I'll add commentary below as req. to elaborate/close-off and keep admin folk off my back ☺