SOLVED

Stack sequences of various size

%3CLINGO-SUB%20id%3D%22lingo-sub-3510768%22%20slang%3D%22en-US%22%3ERe%3A%20Stacked%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3510768%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3BI%20bet%20one%20of%20those%20new%20text%20manipulation%20functions%20may%20help%20do%20this%20but%20I%20don't%20have%20beta%20channel%20so%20here%20is%20my%20solution%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(in%2CInputs%2Cinc%2CSCAN(0%2Cin%2CLAMBDA(c%2CI%2CI%2Bc))%2Cs%2CSEQUENCE(SUM(in))%2Cs-XLOOKUP(s%2Cinc%2B1%2Cinc%2C0%2C-1))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3511359%22%20slang%3D%22en-US%22%3ERe%3A%20Stacked%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3511359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20full%20set%20of%20text%20manipulation%20functions%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20REDUCE(hdr%2CInputs%2CStackedIndices%CE%BB)%0A%0Awhere%0A%0AStackedIndices%CE%BB%20%0A%3D%20LAMBDA(u%2Cn%2CVSTACK(u%2CSEQUENCE(n)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3511743%22%20slang%3D%22en-US%22%3ERe%3A%20Stacked%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3511743%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%20%26amp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20of%20all%20THANK%20YOU.%20I%20wasn't%20far%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Boption%20but%20I%20missed%20something%3C%2FP%3E%3CP%3EI%20can't%20implement%20Peter's%20option%20as%20VSTACK%20isn't%20avail.%20here%20yet%20but%20as%20this%20site%20doen't%20allow%20marking%20more%20than%201%20solution%20I%20want%20to%20mark%20the%20most%20efficient%20one%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%2C%3C%2FP%3E%3CP%3E1)%20On%20a%20significantly%20larger%26nbsp%3B%3CEM%3E%3CSTRONG%3EInputs%3C%2FSTRONG%3E%3C%2FEM%3E%20range%20is%20it%20fair%20to%20assume%20that%20your%20option%20will%20be%20faster%3F%3C%2FP%3E%3CP%3E2)%20How%20do%20you%20generate%20the%20same%20output%20without%20the%26nbsp%3B%3CSTRONG%3Ehdr%3C%2FSTRONG%3E%20row%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3509778%22%20slang%3D%22en-US%22%3EStack%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3509778%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EWith%20Office%20365%2FWindows%2C%20how%20do%20I%20generate%20the%20%3CEM%3E%3CSTRONG%3EExpected%3C%2FSTRONG%3E%3C%2FEM%3E%20dynamic%20array%20from%20the%20%3CEM%3E%3CSTRONG%3EInputs%3C%2FSTRONG%3E%3C%2FEM%3E%20range%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20222px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F380531i281E0A3302DE78A1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20Power%20Query%20nor%20VBA%20please%2C%20LAMDBA%20is%20fine%20though%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3509778%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3512531%22%20slang%3D%22en-US%22%3ERe%3A%20Stacked%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3512531%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20which%20might%20be%20the%20faster.%26nbsp%3B%20I%20could%20write%20a%20Lambda%20function%20to%20replace%20VSTACK%20but%20it%20would%20pretty%20much%20replicate%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bcode.%26nbsp%3B%20My%20REDUCE%20runs%20over%20the%203%20counts%20whereas%26nbsp%3BMatt's%20SCAN%20runs%20over%20the%20total%20of%20the%20counts%2C%20so%20I%20could%20have%20an%20advantage.%26nbsp%3B%20Then%20again%20each%20of%20my%20steps%20involves%20more%20values.%26nbsp%3B%20Perhaps%20I%20should%20try%20some%20timing%20runs%20with%20a%20few%20hundred%20values%20each%20of%20a%20size%20approaching%20100.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESCAN%20and%20REDUCE%20do%20not%20treat%20the%20initialisation%20parameter%20in%20a%20manner%20that%20is%20meaningful%20to%20me%20(neither%20do%20I%20like%20having%20the%20array%20to%20be%20scanned%20as%20the%202nd%20parameter%20following%20the%201st%20parameter%20that%20is%20optional%20as%20well%20as%20being%20largely%20incomprehensible).%26nbsp%3B%20Leaving%20the%20parameter%20blank%20swallows%20the%20first%20term%20of%20the%20sequence%2C%20putting%20in%201%20or%200%2C%20precedes%20the%20first%20sequence%20with%20the%20value%2C%20as%20does%20%22%22.%26nbsp%3B%20Building%20the%20header%20in%20seemed%20to%20be%20as%20good%20an%20option%20as%20any%20and%2C%20when%20it%20comes%20to%20using%20the%20values%2C%20DROP%20will%20trim%20the%20header%20off.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3512726%22%20slang%3D%22en-US%22%3ERe%3A%20Stacked%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3512726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20think%20calculation%20time%20is%20going%20to%20come%20into%20it.%26nbsp%3B%20I%20tried%20280%20numbers%20totalling%20almost%2030%2C000%20rows.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20formula%20returned%20240.5ms%C2%B18.8%20and%20Matt's%20238.8ms%C2%B114.5%20(the%26nbsp%3B%C2%B1%20being%201%20standard%20deviation)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3512813%22%20slang%3D%22en-US%22%3ERe%3A%20Stacked%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3512813%22%20slang%3D%22en-US%22%3EPeter%20deserves%20the%20W%20for%20all%20the%20extra%20work%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3EOr%20you%20could%20mark%20mine%20for%20now%20and%20then%20in%20a%20few%20months%20or%20so%20when%20the%20new%20functions%20are%20released%20come%20back%20and%20update%20it%20to%20Peter's.%20lol.%3CBR%20%2F%3EHonestly%20it%20doesn't%20matter%20as%20long%20as%20we%20know%20the%20thread%20is%20'completed'%20and%20anyone%20who%20searches%20can%20find%20both%20solutions%20depending%20on%20what%20version%20of%20excel%20they%20have.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3512816%22%20slang%3D%22en-US%22%3ERe%3A%20Stack%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3512816%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20one%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DTEXTSPLIT(REDUCE(%22%22%2Cinputs%2CLAMBDA(a%2Cb%2CTEXTJOIN(%22%2C%22%2C1%2Ca%2CSEQUENCE(b))))%2C%2C%22%2C%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3513986%22%20slang%3D%22en-US%22%3ERe%3A%20Stack%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3513986%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANKS%20too.%20Concise%20option%20%2B%20it%20drops%20REDUCE's%20%3CSTRONG%3Einitial_value%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3514045%22%20slang%3D%22en-US%22%3ERe%3A%20Stacked%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3514045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%20appreciate%20you%20compared%20your%20%26amp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Boptions.%20Given%20there's%20no%20significant%20difference%20%2B%20VSTACK%20-%20and%20TEXTSPLIT%20as%20suggested%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B-%20are%20still%20Beta%20functions%20I'm%20gonna%20mark%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bsolution%20for%20now%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERe.%20the%20undesired%20header%20I'll%20keep%20your%20DROP%20option%20in%20mind%20as%20it's%20easier%20than%20doing%20something%20like%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DREDUCE(%0A%20%20%20%20%20SEQUENCE(INDEX(Inputs%2C1))%2C%20INDEX(Inputs%2CSEQUENCE(ROWS(Inputs)-1%2C%2C2))%2C%0A%20%20%20%20%20LAMBDA(u%2Cn%2C%20STACKV(u%2CSEQUENCE(n)))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20again%20to%20all%20of%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3514046%22%20slang%3D%22en-US%22%3ERe%3A%20Stacked%20sequences%20of%20various%20size%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3514046%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3Eit%20doesn't%20matter%20as%20long%20as%20we%20know%20the%20thread%20is%20'completed'%20and%20anyone%20who%20searches%20can%20find%20both%20solutions%20depending%20on%20what%20version%20of%20excel%20they%20have%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAgree.%20Marking%20more%20than%201%20solution%20would%20be%20ideal...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20again%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Super Contributor

Hi

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

 

_Screenshot.png

 

No Power Query nor VBA please, LAMDBA is fine though

Thanks

10 Replies
best response confirmed by L z. (Super Contributor)
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))

@L z. 

With the full set of text manipulation functions

= REDUCE(hdr,Inputs,StackedIndicesλ)

where

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

 

@mtarler  & @Peter Bartholomew

 

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

@L z. 

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.

@L z. 

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)

 

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.

@L z. 

Another one:

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

@Patrick2788 

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

 

Really appreciated

@Peter Bartholomew 

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 

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