Forum Discussion
Stack 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)))
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
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)
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
- 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.
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.