Forum Discussion

Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 15, 2022
Solved

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, LAMBDA is fine though Thanks
Excel for web
Formulas and Functions
office 365
  • mtarler's avatar
    mtarler
    Jun 15, 2022

    Lorenzo 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))
Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 15, 2022

mtarler  & PeterBartholomew1

 

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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 15, 2022

Lorenzo 

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)

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 16, 2022

    PeterBartholomew1 

    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

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 15, 2022
    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.
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 16, 2022

      mtarler 

      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

Resources