Dynamic Arrays

Welcome to this brief Excel video by the FP and a guy. Today I'm just going to talk briefly about dynamic arrays and then show you a cool example. Some things you can do with a stack. Vstack some of the new formulas that they just created. So we're going to start here by just looking at the formulas that came out when they created dynamic arrays. So the first group many of you have heard of, they created an xlookup, an xmatch unique which is what it sounds like. Create a unique list filter, filter some data, sort it a sort by a rand array and then one of my favorite is sequence with the new formulas. You'll see on the left hand side they created 14 formulas there's called this text split text before textafter those will help eliminate A lot of times we use left, right, mid. Find a lot of that can be done with these new formulas. And then there's some shaping formulas that we can use to do things like combine different tables, arrays, ranges together. We can take data from one group, but not all of it. We can drop certain parts from a range, we can expand it, we can choose which columns and rows we want. We can wrap things. So there's a lot of different options. Some pretty amazing stuff we can do with these. Roughly I think 22 formulas here. So as mentioned, dynamic arrays, we've talked about the formulas. Let's go through an example of dynamic arrays and show how they work. So what we have here is we have some names and we want to get the last name. So if I was in using Office 2016 or Excel 2016, I would type vlookup, highlight the name, do the table, you know, right two and say false and then I'm going to drag this down. Well, let's say I wanted to have this all be one row. I wanted it to all be one column in one formula and spill. Well if I'm using Office 365, the new version of Excel, I can highlight this entire range here which is called first names. I named it and then I can highlight my table here. Got my whole table. I can hit two, I can hit false and it automatically updates other examples. Let's say I want to transpose these names right here. So I'm going to highlight it and I'm going to add one see it transposed and it put a zero there. When I add fill it automatically adjusted. The formula lives in one cell. You'll see each one is grayed out. That's a spill and it just keeps going. Well, let's say I already had a name here of Joe and I added Joe right here. And I add Joe to my transpose. So I increase my range. You'll notice I get a spill error. Spill is saying, hey, there's something in the ray. I delete that and now it works. So if I wanted to reference this spilled range, I could come up here and put a dollar sign at the end. And now it automatically referenced it, so it automatically spilled. So that's some very basic dynamic arrays. What I really want to show is an example of what can be done with the new formulas with the hstack. And this idea comes from Excel is fun. Mike Girvin watching one of his videos and also just playing with them. So this is very similar to something he did. A little slightly different data, but showing how we can take and create what I call basically a pivot table. So we're going to start by showing HStack one of the formulas, just how it works. And what you're going to see here is what HSTACK does is it stacks data horizontally. So let's say the Big 12 merged with the Pac 12 and with San Diego State and Boise State from the Mountain West. So what I'm going to do is I'm going to highlight each of these and let's just say in case I want to add more data, I'm also going to highlight this next one. And so now when I close it, I've just created all of those combined and you can see these are zero. So let's say we add another school. Let's say they're lucky enough to add Nebraska or unlucky enough, depending on what you think of Nebraska. Come over here. You can see Nebraska has been added. Notice how it automatically stacks the data. So what this allows us to do by stacking is we could basically create a dynamic array that functions the same as a pivot table. So now I want to show you example 2. I got this table here and we're going to start by putting this table into a pivot table. So I've come insert pivot table from table range. We got our sales data. We're going to put it in this workbook. I'm going to stick it here, right? And I'm going to put amount sold there. And I'm going to put salesperson here. Notice similar to this, but it has a total. So what I'm going to show you is how we can create this. And then I'm going to show you how we can create a total that will Automatically update. Doesn't even need to be in a pivot table. So what I want to do, I want to say hstack. So horizontal stacking. We're going to stack salesperson, amount sold. And I'm going to close that right. So now I have that. I'm going to go ahead and bold it because I want it to look the same as above. So next I need to get each of these sales reps. So if I wanted to get just the sales reps, I'm going to do another hstack and I'm going to go ahead and highlight this range. Let's see what happens. Well, I did get all the sales reps but I didn't get a unique list. So if I want a unique list, notice unique is one of the new formulas. So now what I'm going to do is I'm going to come in here and I'm going to go unique and wrap that inside this hstack. And now I got a unique list. We're going to go ahead and take that bold off. But we can see I have reps 1 through 11. Now what I want to do is, okay, I need to get a way to get the amount sold. So the amount sold is going to be the second horizontal column. So I need a stack on top of this. So I'm going to add a horizontal stacking. I'm going to hit alt enter and put it on a new row. And if I want to get the amount sold, typically I'd use a sumifs. So let's go ahead and do a sumifs. We'll take that. That would be my sum range. Then I need my criteria range which will be the salesperson. Now I need the criteria which is this right here. But I can't highlight that list. So what I'm going to do is I'm going to recreate that. So it's going to be the same as above. It's going to be the unique list right in the criteria. And we got an error because I didn't close everything off. I want three because I had the uniques and the sumifs. So we'll add the third one there. And now you can see just created this list. Now it's sorted different because I used a sort up here and so sales rep 10 came here. In this case, I didn't use a sort. Now the only thing I'm missing is I don't have a grand total. How could I add a grand total and make this all one formula? So the headers and everything is one. So we'll see here. This is one formula. I'm going to walk you through how you would create that. We're going to use an hstack, a vstack, a sort, a unique, a sumifs combined together to create this. So first I want to show you. Let's say I add sales rep 11. Sales rep. You can see it already matted it. And let's say it's 85,000. Now, you do notice the formatting doesn't automatically update. You could do some things dynamically to do that with conditional formatting to try to create, you know, to have it dynamically update. But on its own, it's. It's one of the disadvantages of arrays is they don't. They unfortunately don't automatically update. But now you can see their cells are up 11. I can add 12, 13, 14. So how do we create this? So we know we did the first formula of an hstack. We did an HSTACK here. If I want to do a total on its own, I could do that. So I could say hstack total. So that's my first column. My second column would be a sum of this right here. Right. And so again, I need to wrap it twice. Bad habit of mine for getting all my parentheses. Fortunately, Excel lets me know. And you can see there's the million. So now using this example, you can see I did that there, I did that there, and I created the total. So now what I need to do is I need to combine those three together. Well, if I want to combine three horizontal stacks into a vertical stack, I can use vstack. So let's get started. So we're gonna say vstack and then we're gonna do our hstack, But I'm gonna put that on a separate row so you can see each step on its own row. I want salesperson first, I want amount sold second. So when I do this. And again, bad habit. We got it. So we got the first part. So we got step one. So now is the next step. I need to add another stack. So I'm going to come here and I'm going to do an HSTACK again. And the first thing I want is that unique list of salespersons. So we're going to type unique. We're going to come here and we're going to highlight that. Let's just close it for right now. And let's close the vertical stack and see what happens. Need one more parentheses because I had the unique. And so now we can see I've got this part done, but I don't have the amount sold. So that tells me I need to add that to my formula. So we're going to go back to here. So we're back to the hstack. And now we're going to add our sumifs, same as we did before. We do our amount sold. We're going to add our salesperson and then we're going to do unique and throw that there. We're going to close it. Our sumifs. Now we'll close our hstack. We'll close our vstack. And I missed one. We want one more. And there we go. So now you can see we're going to clean up the formatting here real quick because you can see it didn't clean that up. It used what was there last time. But now what we have is we have a vertical stack with two stacks. We stacked this one and we've stacked this as our second vertical array. Now what we need to do is we need to add that total, which is our third hstack. In this case, we're just going to type manually total. So we've added that. Now we just need a sum of this table. So we're going to say sum. We're going to come here, we're going to click that. And as normal, I always forget to add each of these. So there we go. We've added them and now you can see we've created what's above. We'll bolt that. We'll do our dollar sign there. Now we'll add sales rep 12 and we'll say 125,000. And let's add sales rep 12 again and say it's 250,000. And maybe that is April, you know, 4, 1, 20, 22. We don't have to put a date because it's not using the date to sum these. So so there you can see it automatically adjusted. So that's really cool. Those are the type of things you can do with dynamic arrays. There's all kinds of shapings and combining. I'm going to call the video there and give you guys the chance to watch it.