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))

10 Replies

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

      Patrick2788 

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

       

      Really appreciated

    • 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)

         

  • mtarler's avatar
    mtarler
    Silver Contributor
    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))

Resources