SOLVED

Stack sequences of various size

Super Contributor

Stack sequences of various size

Hi

With Office 365/Windows, how do I generate the Expected dynamic array from the Inputs range?

No Power Query nor VBA please, LAMDBA is fine though

Thanks

10 Replies
best response confirmed by L z. (Super Contributor)
Solution

Re: Stacked sequences of various size

@L z. I bet one of those new text manipulation functions may help do this but I don't have beta channel so here is my solution:

``=LET(in,Inputs,inc,SCAN(0,in,LAMBDA(c,I,I+c)),s,SEQUENCE(SUM(in)),s-XLOOKUP(s,inc+1,inc,0,-1))``

Re: Stacked sequences of various size

With the full set of text manipulation functions

``````= REDUCE(hdr,Inputs,StackedIndicesλ)

where

StackedIndicesλ
= LAMBDA(u,n,VSTACK(u,SEQUENCE(n)))``````

Re: Stacked sequences of various size

First of all THANK YOU. I wasn't far from @mtarler option but I missed something

I can't implement Peter's option as VSTACK isn't avail. here yet but as this site doen't allow marking more than 1 solution I want to mark the most efficient one

Peter,

1) On a significantly larger Inputs range is it fair to assume that your option will be faster?

2) How do you generate the same output without the hdr row?

Thanks again

Re: Stacked sequences of various size

I don't know which might be the faster.  I could write a Lambda function to replace VSTACK but it would pretty much replicate @mtarler code.  My REDUCE runs over the 3 counts whereas Matt's SCAN runs over the total of the counts, so I could have an advantage.  Then again each of my steps involves more values.  Perhaps I should try some timing runs with a few hundred values each of a size approaching 100.

SCAN and REDUCE do not treat the initialisation parameter in a manner that is meaningful to me (neither do I like having the array to be scanned as the 2nd parameter following the 1st parameter that is optional as well as being largely incomprehensible).  Leaving the parameter blank swallows the first term of the sequence, putting in 1 or 0, precedes the first sequence with the value, as does "".  Building the header in seemed to be as good an option as any and, when it comes to using the values, DROP will trim the header off.

Re: Stacked sequences of various size

I don't think calculation time is going to come into it.  I tried 280 numbers totalling almost 30,000 rows.

My formula returned 240.5ms±8.8 and Matt's 238.8ms±14.5 (the ± being 1 standard deviation)

Re: Stacked sequences of various size

Peter deserves the W for all the extra work
Or you could mark mine for now and then in a few months or so when the new functions are released come back and update it to Peter's. lol.
Honestly it doesn't matter as long as we know the thread is 'completed' and anyone who searches can find both solutions depending on what version of excel they have.

Re: Stack sequences of various size

Another one:

``=TEXTSPLIT(REDUCE("",inputs,LAMBDA(a,b,TEXTJOIN(",",1,a,SEQUENCE(b)))),,",")``

Re: Stack sequences of various size

THANKS too. Concise option + it drops REDUCE's initial_value

Really appreciated

Re: Stacked sequences of various size

Really appreciate you compared your & @mtarler options. Given there's no significant difference + VSTACK - and TEXTSPLIT as suggested by @Patrick2788 - are still Beta functions I'm gonna mark @mtarler solution for now

Re. the undesired header I'll keep your DROP option in mind as it's easier than doing something like:

``````=REDUCE(
SEQUENCE(INDEX(Inputs,1)), INDEX(Inputs,SEQUENCE(ROWS(Inputs)-1,,2)),
LAMBDA(u,n, STACKV(u,SEQUENCE(n)))
)``````

Many thanks again to all of you

Re: Stacked sequences of various size

it doesn't matter as long as we know the thread is 'completed' and anyone who searches can find both solutions depending on what version of excel they have

Agree. Marking more than 1 solution would be ideal...

Thanks again