Exciting NEW Excel Functions for Compiling Data - VSTACK & HSTACK!

In this video, I'm going to cover two exciting new text manipulation functions available to Microsoft 365 users, namely VStack and HStack, which work to combine arrays, either arranged vertically with VStack or horizontally with HStack, into a single array. Now the syntax is fairly straightforward. As you can see, they simply take the arrays or cell ranges that you want to append. However, there are some clever techniques we can use to enable stacking data from multiple sheets, sorting the data, specifying headers, filtering the data, handling errors, and more, which I'll cover in this video. And be sure to watch to the end, where I outline the limitations. In its simplest form, VStack combines multiple arrays of the same number of columns. For example, here I can stack these two tables into a single array. Notice VStack handles the different number of rows in each array without a problem. But what if you want to include the column headers? Column headers can be added to the stack by entering them in the first array argument. They're wrapped in curly braces and each header is surrounded in double quotes and separated by a comma. Alternatively, you can reference an array that contains the labels. Just make sure it's the first array, or as I've done here, the labels in the first table in your stack. We can nest VStack functions inside one another to enable sorting of data using the sort function separate to the headers. So we'll add the headers first, and then I want to sort my VStacked array. So we're going to reference the ranges, and then I need to specify the sort index. So let's say I want to sort by the revenue, and that's in the third column, and I want to sort it in descending order, so that's minus one. Close sort and close VStack. If the array is being stacked to different sizes, VStack will return na errors in place of the missing data. For example, here the second array only has one column, so VStack returns errors for the rank column of the final array. Wrapping the formula in ifna enables you to specify something else in place of the errors. For example, I can use two double quotes, so the cell appears empty. However, you could also enter some text in here like missing data. Omitting duplicates from the final array is super easy with the unique function. Close parentheses on VStack and close unique, and let's say I want to sort it as well, so let's wrap it in sort. That's just going to sort it based on the name column in ascending order. You can stack data spread across multiple sheets. For example, in this file I have data for 2020 on this sheet, 2021 on this sheet. Notice that the columns are the same, but the number of rows are different. As you can see, 2022 only has four rows, 2021 has six rows, and 2020 has five rows. I can use VStack with a 3D reference to stack them all in one array. I select the array. Now I need to expand the array down to row six because remember 2021 data goes down to row six, and then holding down shift, I simply select the last sheet in my 3D array. Close parentheses on VStack and press enter, and there's my data. Now because I've allowed for the table containing the largest number of rows in my range, it results in zeros being returned for the tables that don't contain data on rows five and six. We'll look at hiding these blanks in the next example, but first another option if you have your data stored in an Excel table is simply to VStack each table, and in doing so, if the tables grow, VStack will automatically include those new rows in the final stacked array. It's not quite as quick and easy to write, but it gets around the zero rows issue. The previous example contained some empty rows, which resulted in zeros throughout the final array. We can use the filter function to exclude those rows from our stack. Now the first argument for filter is the array that I want filtered. In this case, it's the 3D reference. Let's grab that. Remember going down to row six and through to sheet 2022. That's my first argument. That's the array. The next document is a logical test filter, so it knows which rows to include. Again, we're going to use VStack, and we're just going to look at column A down to row six through to sheet 2022, and we're just going to check that those rows are not equal to blank. So that's my criteria for filter. Press enter, and there's my filtered stacked array without the blanks. Similar to the previous example, we can filter for specific criteria. For example, let's say I want to return plus a filtered array consisting of the stacked 3D array of the three tables through to sheet 2022, filtered for the category clothing. So we're going to VStack the categories three to 2022, where they equal clothing. Close filter and close VStack. So here I've used VStack three times to get a filtered list from a 3D array. HStack is the horizontal stacking equivalent of VStack. So we'll just look at one scenario for completeness. Here I have two arrays vertically arranged, and I can use HStack to combine them into a horizontal array. I'm going to include the headers, and then I'm going to add some double quotes just to allow a blank column in between my two arrays, and then select the second array, close parentheses. Now because that blank column only contains one column, if you like, instead of two in line with my two arrays that I'm stacking, I need to wrap it in FNA and hide any errors. We'll close parentheses, and there's my two horizontally stacked arrays with the blank column in the middle. One of the advantages of the stack functions is they can be used to make 3D references work with any function that doesn't have support, except those functions that require a range as input. Remember a 3D reference is one that references multiple sheets, like we looked at earlier, referencing the sheets 2020 through to 2022. Another common 3D formula is to sum the data on those three sheets, like so, and we're going to expand to row six, holding down shift, selecting sheet 2022, close sum, and there's our result. Now you can do this because the sum function supports 3D references. There are a load of other functions that support 3D references, and you can see a link to that here in this file, which you can download from the link in the video description. Now if you wanted to sum the data for the clothing category on those three sheets, you might try and write a SUMIF function, and let's just take a look at what happens if we try that. So our range is the category down to row six, and it's a 3D range, where the category is clothing, and the range we want to sum is the sales down to row six, and it's a 3D range, close parentheses on SUMIF, and you see we get an error, and that is because the first argument of SUMIF is a range. 3D references don't work with functions that require a range as input, and you might try and get around this using VSTACK, and let's just see what happens if you try that. So we're going to VSTACK the ranges, so we're going to VSTACK the category, 3D range, where the criteria is clothing, and then we're going to VSTACK the sales values in a 3D range, close VSTACK, close SUMIF, and you can see we can't even enter the formula, it just won't accept it. So what do we do? Well, there are a few options. First of all, we can use SUMPRODUCT, and we can VSTACK the arrays, so we're going to VSTACK the categories in a 3D range, where they equal clothing, and I need to add in a double unary here to coerce the logical test into its numeric equivalents of 1 and 0, so we're going to close that in parentheses, times VSTACK of the sales in a 3D array, close VSTACK, close SUMPRODUCT, and press enter, and there's our value. We can write this formula slightly more succinctly. Instead of SUMPRODUCT, we can just use SUM, and you get the same result. Now VLOOKUP and XLOOKUP, on the other hand, do accept VSTACK in the array argument. For example, let's say I want to look up socks, and my table array, I'm going to use VSTACK to return the 3D array, so we're looking up socks in this array through to 2022, close parentheses on VSTACK. Which column do I want returned? Well, it's the second column in that range, and I want an exact match. Close VLOOKUP, press enter, and there's the value for socks. If we take a look, we've only got socks on the first sheet, 2020. There's no socks on 2021 or 2022. Of course, we can also use the more robust XLOOKUP formula. Again, we can look up socks, and this time we're going to VSTACK, first of all, the product column through to 2022, and then we're going to return another VSTACK of the sales values through to 2022, close parentheses on VSTACK, close XLOOKUP, press enter. It's slightly longer, but as I said, XLOOKUP is more robust, and if you have the VSTACK functions, then you should be using XLOOKUP. I hope you found this tutorial useful. You can download the XL file for this lesson from the link here, and if you like this video, please give it a thumbs up and subscribe to our channel for more, and why not share it with your friends who might also find it useful. Thanks for watching.