in this video we're going to use vstack function to combine multiple ranges tables worksheets
whatever you want to apply it to so i have these three tables right now
let's try to combine that data together so if i for example use v stack we'll have this
first array argument so i'm going to go ahead and select the data in this first
table comma then we can continue adding more so if i do the second range close parenthesis hit enter
you'll see how we get both of those combined this first column
is not formatted as dates so we can go ahead and format it as dates
and if we wanted to also add this we can go back here do another comma and literally just
select that third table and as you can see we can keep comma separating and adding more
if i hit enter it will combine them together now what's interesting about this function is that
your ranges don't necessarily have to be the same size so for example for the second range
if i only select these two columns and don't include sales close parentheses and hit enter
it will create a range of the largest size so we got this one where it was three columns
and the rest it's gonna fill with nas so i'm gonna go back and actually add
that third column overall this seems to be pretty straightforward as you can see
so some issues you're gonna have here are going to be with blanks and updates so let's
start with blanks and see what problems we may face so let's say for example a couple of these
states are not available here so if i do this if you look at our results instead of getting
us those blanks we'll actually just get zeros which is not great so the way we can work around
this we can go back and we can wrap each one of these ranges in an if statement so we can say if
this range equals blank just one equal sign blank comma then return a blank
comma otherwise just return the range itself so that same range we did and close parentheses
and then we're going to repeat the same for the second range so we can do if this range
equals blank comma blank comma otherwise use the range and then we can close parentheses hit enter
and now you can see that blanks are actually blanks so if i go ahead and remove this one too
from here you can see it shows up as a blank here now as you can see this whole syntax is not great
with this if statements especially if you had to do let's say 10 ranges and combine them together
you would have to repeat this like 10 times so one way we can get around this
we can basically create a lambda function to do this for us so let's just create that
on the side so for example if i do lambda and as a parameter i'm going to accept that range
so i'll just call it rng comma and then as a calculation we'll say if that rng
equals blank comma then return blank double quotes comma otherwise return the range
itself so this would be our formula there now we're going to close that lambda function
and if we wanted to run that we could open parentheses and select range like this close
parenthesis and hit enter and that should get us the results and again these are not formatted
but again this is no better than the if statement we had before but what we can do
we can get rid of this calling part and grab this lambda function copy and create a nice function
to call for this so the way i can do that i can go under formulas on top here go to this name manager
and create a new name so it seems like i already have some named ranges but
it doesn't matter so i'll go ahead and create a new name
and i'll just delete whatever's here and paste that lambda function and on top here
as a name we're going to give this function a name so i'm going to call this handle blanks
you can call it something else if you want press ok and now what i should be able to do is instead
of doing all of this i should be able to just simply go here and do a handle blanks and then
select this range and if i enter i'm going to get the same result which means that now i can also
use this if i delete it from here in this v stack so instead of doing all of this
handle blanks and then select my first range close parenthesis then comma
then again handle blanks and then select the second range comma and then we could keep comma
separating like this and add our ranges i'm gonna add the third one too like so and then select this
close parenthesis and close parentheses finally for the whole v stack over here
and at this point if i hit enter we should get our combined data so at this point the
issue we may have with this is that as we add more data to these tables we may want this to
expand and add that information to this now at the moment if i just go here and add a new transaction
that's not going to show up here because our range does not contain that line we can go back and
manually expand this and make sure it handles that but it's not gonna happen automatically
so i'm gonna hit escape so what we could do we could convert these two tables so i can just
grab this first one go to insert and click table and we have headers on top there they are i'm
going to press ok it's best to name your tables in a logical way so by default excel will apply
names like table 1 table 2 table 3. now the way you do that after you create the table first if
you want to change the style here you can apply a different style as you can see but you could also
just remove that style by simply just going to this light or non option basically so that removes
the style if you don't want to keep it and then all the way on the left here we have the name
of this table so i can call this whatever i want now it's called automatically table one
so i'll just call it like sales one make sure you don't add any spaces or special characters here
you can use underscores if you want i'll just call this sales one because i don't have a good
name for this hit enter and then i can select the second one go to insert and do table again
and okay and yet again i'm gonna open this and do light or you can keep the design and go over here
and call it sales two and hit enter and then we can repeat these for all of them you can either
select the table and go insert table you can also do control t which would be the shortcut to get
this box press ok and again i'm going to call it sales 3 for this one and remove the default design
so now because these are tables i could use those table names in my formula i can go back
to my formula here and instead of this right here i could simply just say sales one instead of this
i can do sales two basically the name of the table and this would be sales
three so now i got first table second table third table and if i enter that should pretty
much get us the same thing now it did also include that extra line i had here we might want to add
some headers to our combined results over here just to make it look better but at this point
if i go and add anything to any of these three tables so for example if we add one here
you can already see how it's updating over here as i type
and there we go so that's updated here as we add more so to any one of these tables
because it's an expandable range as i add more data it will be updated in this results
right away now you may have these tables all located here right next to your combined data
but most likely if you're combining this it's probably a situation where you have these tables
spread out across different worksheets like this like this one i have here then i have here
then i have this one here so it's the same structure of a table just different data so what
i could do i could go to this first one and i'm gonna go ahead and convert this to a table again
i'm gonna go ahead insert table now remember that you cannot use the same name all over again for a
table so since i already have a table like sales one i couldn't call this sales one again but i'm
gonna go here as a table name and i'll name this table so i'll just call it like partial one then
i'm gonna redo this for all of these i'll go here grab the second table and make a table out of it
call it maybe partial 2
grab the third one
table
light you can also remove this filter button if you don't like them
so i'll call it partial 3. so now i got three tables i called them partial one partial two
partial three and finally i'm gonna add another tab here call it combined data
or something and i'll start by simply just copy pasting these headers from one of these tables
ctrl c go back here ctrl v and then here we'll do our v stack
and then we'll use that function to take care of blanks i named the function handle blanks then
you could go to data part one and select the table if you wanted to but i have a name for
that table and it's called like partial one so i'll just use the name
a lot easier comma and then we'll do a handle blanks partial two and by the way the way i'm
auto completing the rest of this without typing is by hitting tab on my keyboard
the arrow keys tab see there we go and then finally once i got all the tables
i want to be combined i can just close that v stack parentheses here
that function hit enter and i can now just click this column a add some date formatting to this
and we should have our results and now as we keep adding to any of those tables
this should dynamically update and give us our combined results and that should do it
for this video thanks for watching please subscribe and i'll see in the next one