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

In my previous video I uncovered something odd about VSTACK. Turns out, VSTACK is doing what it's supposed to do. I show that in the video. And that leads to something fascinating: EXPAND.

The EXPAND function on its own seems strange and pointless. However, in this situation EXPAND saves the day in a practical example.

We have a start date and number of days and want to list each date. Once their listed, we'd like them to be compiled in a single array. The problem is, the rows don't have an even number of dates. That's where EXPAND comes in! EXPAND makes all rows equal so that the data can be both, dynamic and compiled in a single array.

You'll also see the TEXT function used to convert a number into a date.

#DynamicArrays
#EXPAND
#ExcelTips

For a list of my Excel courses at Lynda/LinkedIn:

There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

My book: Guerrilla Data Analysis 3rd Edition

[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!

[nsanch]

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

[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!

[DJDadzGaming]

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

[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.

[stevemrasek]

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.

[chilaw]

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

[williamarthur]

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

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

[srider]

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

[dontown]

Great hints! Interesting kaleidoscope.

[wallacefrancis]

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

[roupenyeremian]

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?

[flexyourdata]

Great explanation! Thanks, Oz.
- Owen

[ExcelUpNorth]

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

Also cool kaleidoscope!

[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. 🤗

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