Practical use for Excel's EXPAND function | VSTACK mystery solved | Dynamic Arrays

[@DimEarly]

Nice! I was wondering what you could do in a similar situation where there wasn't a handy column with the size of each array (which there might not be depending on where the data comes from). I started poking around, and in the process I learned a new trick about dynamic arrays, so I thought I should share: Suppose you have the same column of arrays starting in F2:F8, but expanding to different sizes. You can use =BYROW( F2:F8, LAMBDA(x, COLUMNS( x# ) ) ) to return an array of the sizes of each of the spilled arrays - either as an output into a cell, or straight into another function (e.g. you can wrap it in a MAX and use it with OFFSET to grab the whole range). You can read the LAMBDA as 'a function that takes a cell, and returns the number of columns in an array anchored on that cell' - I wasn't sure you'd be able to add a # to a variable name like that, but you can! : ) The rabbit hole this led me down had a few other discoveries in it too... I think I might need to make a quick video tonight!

[@OzduSoleilDATA]

YES! Make a video. You got me curious. 🤔

[@DimEarly]

@@OzduSoleilDATA It’s coming! Just recorded… hopefully up tonight…

[@Picla_Peremohy]

Very interested to watch as well

[@Reduce_Scan]

@@DimEarly I will listen to the clip tomorrow , this is my attempt I hope for your opinion =DROP(REDUCE("",SEQUENCE(ROWS(C2:C7)),LAMBDA(A,D,IFNA(VSTACK(A,SEQUENCE(,INDEX(C2:C7,D),INDEX(D2:D7,D))),""))),1)

[@wallacefrancis372]

The 𝙅𝒐𝙝𝒏 𝑾𝙞𝒄𝙠 𝙤𝒇 𝑬𝙭𝒄𝙚𝒍 told me to tell you he knows you.... Mr. LJ of Excel. I have absolutely no idea what this means, but I was also told to make some subtle inference (that I couldn't think of a way to do) that this bizarre statement may prove significant in 2024. ¯\_(ツ)_/¯

[@nsanch0181]

That was an interesting one. I was just messing around with Expand function today and I was happy to run into your example.

[@williamarthur4801]

At last a use for expand, well done. And that Kaleidoscope, NOW I know what I want for Christmas.

[@GrainneDuggan_Excel]

So many tips in this video such as you didn't need VSTACK at all. Thanks to Owen Price for pointing out EXPAND and to you Oz for demonstrating it. I am astonished you managed to get such fabulous video of the kaleidoscope outside of a professional studio! Beautiful. Love the confetti in Camtasia too!

[@OzduSoleilDATA]

I recorded the kaleidoscope through my phone, and edited out the parts where, for example, my finger got in the way. It wasn't easy, but it was fun to share.

[@chilaw2004]

Great video Oz! And the prettiest kaleidoscope I have ever seen!

[@DJDadzGaming]

Love the video and the alternative solutions in the comment sections. Truly, makes me want to learn more!

[@stevemrasek5824]

I learn so much from this channel. Clear and concise. Thank you!

[@IvanCortinas_ES]

Curious arrangement with the use of the EXPAND function. The kaleidoscope is very cool.

[@ironicdaemon]

Great use for EXPAND() - thanks for the practical example! Unfortunately while I love the TEXT() function, it's frustrating that it's only practical if you can assume all of your users have the same regional settings - e.g.. a day is always a "d" or a "." is the decimal separator.

[@dontown1531]

Great hints! Interesting kaleidoscope.

[@OzduSoleilDATA]

😁

[@ihave_ajob]

Great video!

[@OzduSoleilDATA]

Thank you!

[@ExcelUpNorth]

Thanks for the video! Dynamic arrays are something I’m interested in learning more of. Also cool kaleidoscope!

[@OzduSoleilDATA]

YES! Learn more about dynamic arrays. They're solid.

[@komanguy]

Great tip! Thanks a lot!

[@flexyourdata]

Great explanation! Thanks, Oz. - Owen

[@OzduSoleilDATA]

Owen! Thank YOU. 😃

[@roupenyeremian6801]

Great vid Oz. I can see what the EXPAND function is doing. So, if the maximum days say is 8, you want each row to have that many columns although some may be blank. So in the bottom array, when you check each row it shows 8 columns. Essentially, it's preventing some values from being cut-off. Two questions then: (1) how does VSTACK come into play then (or does it)? and (2) you have a start date, but the days are not necessarily consecutive (or in sequence), what then?

[@wallacefrancis372]

Nice video. I wish i knew how to use Excel.

[@srider33]

2:10 the moment I started typing this comment thinking you overlooked something.... but didn't. Nice....

[@Reduce_Scan]

I take Idea for Bhavya Gupta and make change =LET(c,C23:C28,d,D23:D28,a,ROW(c)^0*SEQUENCE(,MAX(c))+d,IF(a>d+c,"",a-1))

[@OzduSoleilDATA]

Fascinating

[@stevenflax]

Nice!!

[@JoseAntonioMorato]

Dear Oz. Report in a single cell, without using the EXPAND and VSTACK functions: =LET( x,MAP(Tabela1[NAME],Tabela1[DAYS],Tabela1[START],LAMBDA(a,b,c,TEXTJOIN("*",,a&" "&SEQUENCE(,b,c)))), y,TEXTSPLIT(ARRAYTOTEXT(x)," ",{"*",";"},1,,""), VSTACK({"Name","Days"}, HSTACK(CHOOSECOLS(y,1),TEXT(CHOOSECOLS(y,2),"dd-mmm-aa")))) I hope you enjoy. 🤗

[@OzduSoleilDATA]

LORDY LORDY! This is thrilling. 🧐 AMAZING!

[@Reduce_Scan]

My try to solve for One Formula to all =DROP(REDUCE("",SEQUENCE(ROWS(C2:C7)),LAMBDA(A,D,IFNA( VSTACK(A,SEQUENCE(,INDEX(C2:C7,D),INDEX(D2:D7,D))),""))),1)

[@spilledgraphics]

Second comment! 🔥

[@spilledgraphics]

8:27 = 🤯🤯 amazing Oz! From an analogy perspective, what you see on this beautiful kaleidoscope is what one can start seeing when combing the features of Excel.

[@OzduSoleilDATA]

@@spilledgraphics MAAAAAN! I thought you were here for the Excel, but you're here for the doggone kaleidoscope. 😄

[@73p3ro]

Easy, chocolate milk Y'all.

[@OzduSoleilDATA]

AH! You remember that! 😄

[@73p3ro]

​@@OzduSoleilDATA I remember almost everything, I'm a louse from your channel. I try to keep up with the beasts of Excel! And nigga, you are fuck!!! Man, you are badass!!!