Jun 15 2022 04:40 AM - edited May 12 2023 11:58 PM
Hi
With Office 365/Windows, how do I generate the Expected dynamic array from the Inputs range?
No Power Query nor VBA please, LAMBDA is fine though
Thanks
Jun 15 2022 07:53 AM
Solution@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))
Jun 15 2022 09:20 AM
With the full set of text manipulation functions
= REDUCE(hdr,Inputs,StackedIndicesλ)
where
StackedIndicesλ
= LAMBDA(u,n,VSTACK(u,SEQUENCE(n)))
Jun 15 2022 10:35 AM
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
Jun 15 2022 01:13 PM
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.
Jun 15 2022 01:48 PM
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)
Jun 15 2022 02:05 PM
Jun 15 2022 02:11 PM
Jun 15 2022 10:34 PM
Jun 15 2022 10:50 PM
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
Jun 15 2022 10:55 PM
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
Jun 15 2022 07:53 AM
Solution@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))