- All right, so if you're using Google Sheets
and you have multiple sheets,
so I have here January, February, and March,
and you want to take the data that's in those
and join them together into one long table,
we're going to go through a couple of different ways
to do that right now.
Each of these ways use just built-in functions
that are in Google Sheets, there's nothing crazy.
Each of the techniques that we're going to go over,
will do it a little bit differently
and you can choose which one you want to use
based on exactly what you're trying to do.
But let's just get into it, so here we go.
(electronic upbeat music)
So let's go to this blank sheet that I have
and what we're doing, we're acting like
we're summarizing the first quarter, okay?
So we want to put together January, February, and March
and each of these three tables has the same column headers
with the same type of data
so they can just naturally be joined together.
And the first solution that we're going to do
is a way to put them together
that maintains the original order of the data in the sheets.
So it's just going to stack them
January, February, and March.
And you would do that, you would start out
with the filter function.
And the filter function, what it wants is,
hey, tell me the data that you want me to filter,
so that's the range, and then tell me how to filter it.
So the range, this is where it gets a little bit tricky
and you have to remember to do these curly braces.
So when you do one of those to open up the formula,
it's telling Sheets that you're going to give it an array,
so multiple ranges to look at at one time.
So if you just look at the help for the filter function,
it looks like it just wants one range.
So you have to do these curly brackets
and then you specify the ranges.
So all of these ranges are on different worksheets.
So you have to, there's two ways to select them
that I would use.
The easiest way, though, while I'm still in the formula,
so the cursor is still blinking up there
and it's waiting for something,
just go to the January worksheet right now
and select this table.
And on the first table that you do,
you can go ahead and select the header, it'll pull it in,
just don't do that on the subsequent tables
'cause then you'll repeat the header.
But there's something else that I'm going to do
in this formula is that I'll take the bottom row number
or that, the closing row number, the 17,
and I'll just take that off completely.
And then that way, if new data is added or subtracted,
it will pick that up with this function.
So we're ready for the next worksheet now.
So you separate these ranges with a semicolon.
And we'll, I'm going to do this,
the way I think is a little bit easier,
I'm going to copy, oops, that's not easy.
All right, the formula kind of jumped around on me there,
but it's the same one, I'm going to copy this range.
And I know I want January, February, March,
so I'm just going to use control + C to copy it,
paste it, semi-colon and paste it,
and then I'll go in and change these other sheet names.
So the second one's not January it's February.
And I grabbed the header on the first one.
So all of these tables are starting at row one.
So for the second one started at row two.
So you don't grab the header twice
and then change January to March.
And these are my three ranges.
Let me make that at two also.
So once you specify all the sheets
that you want to bring in,
use the closing curly brace to tell Google Sheets
I'm done specifying the ranges.
And then so, in the filter function,
we've completed specifying the range,
if you look at this little helper text here,
so now we want a comma to tell it
that we're ready to specify the conditions.
So I'm going to tell the filter function,
I told it what I want it to filter
and now I'm going to tell it, of that data,
here's what I want.
And what you actually want though, is all of it, okay?
Except the blank rows.
So we're going to take the function called is blank,
but we're going to turn it around.
So we're going to say not,
so just return the opposite, and then do a parentheses.
And then we're going to nest the is blank function
inside of here
and then we're going to actually use the curly braces again
to say that we're going to do a group of ranges.
Now just copy these to save yourself some time
and paste them and then we're going to do
some minor surgery on them.
All you want to do is specify one column
in each of these ranges, the filter function just once,
one column to look at, to use the criteria.
So let's say it's column,
so you have text in the first column,
then numbers and numbers,
let's say the text is the most meaningful to use,
but you could really use any of them in this case,
because we're just stopping when it's blank,.
But let's do, A-one to A,
that'll just make this one column,
A-two to A
and A-two to A, oops, A-two to
and A-two to A, for the third sheet as well.
So let's close that off with the curly braces and then close
and then close off, we're still inside
of the is blank function, so we'll close that
and then we're inside of not function, we'll close that,
and now we're back to filter.
So we need one more parentheses to tell it that we're done.
So if you press enter now,
it's going to take us back to the quarter one spreadsheet
and it's going to return all three of these tables.
So let's take a look here at what happened.
It grabbed the header from the first table.
So let's look at this first table, let's,
it says department, then it says jewelry and books,
and it goes down 17 rows
and then we're going to add in another nine,
'cause we're not using that header,
so we're at 26 and then we're going to add in another 16.
So that's 36, 42 I think, so let's look at quarter one.
We have 42 rows and it does start out with a department
jewelry and then books.
So we've joined three tables together
from three different worksheets.
We're going to go over a couple of different ways
to do that and then we're going to bring in a table
from another file using a similar technique,
but I'll just show you how to link those
when we're done here.
So if you want to jump to any of these different techniques,
just use the little chapters down at the bottom of the video
or go in the description, we'll have timestamps.
But let's move on to the second technique.
So this one puts all three together
and it keeps the original order, right?
So our second technique,
it's going to be a little bit shorter formula
and what it's going to do, it's going to sort them
based on whatever column that you pick.
And here you need to grab a header row
because it's going to sort your header, too.
So you don't necessarily want your header to end up
at the bottom or in the middle.
So let's just go grab January's header,
hit control + C on my keyboard and I'll go to
first quarter tab and I'll put the header in there.
And then now we're going to use a sort function.
So this is another built-in function like filter.
And don't confuse these with the way that you sort
and filter from the menus.
So if you want to do data filters here,
and sort is here as well,
but the built-in functions allow you to do things
a little bit differently.
So we're going to say, sort
and now it wants that range again,
so I'm going to cheat here because I already did the range,
I don't need to redo it,
and I'm going to grab it from this filter function.
So I'll go in here and I will grab this range.
We need to modify it just a tiny bit,
but we'll talk about that.
Okay, so I just did control + C to copy it.
And now I'm going to say, sort.
Now I need that beginning of the curly brace
and I'm going to paste this range, close it off.
But then I said, we need to modify this a little bit.
All we need to do, we don't want to grab the header row,
so that's row one.
So here for A-one, you want to change it to A-two.
So this is grabbing all three worksheets
and then there's not much that sort needs after this.
So what you want to do is just specify
the column that you want to use,
so let's do this in column number one
and then whether or not it's ascending or descending,
so we'll just say true,
change that to false if you want it to sort the other way,
and that's all we need.
So we'll close this off with an ending parentheses
and we'll hit enter, and there we go.
So it joined these tables.
Let's scroll down to see if they're the same length.
They're the same length and it, but it's sorted them.
So it sorted them by the first column.
As you see, it's a little bit shorter formula,
but the result is completely different.
So that's two different ways.
You can choose whichever one of those that you want.
But now there's a third way to do it.
If you want to get rid of duplicates
when you're putting these together,
you know, in this data, you probably want
and I acted like this as sales data,
but maybe there's something that, when it repeats,
you only want the first one.
So in that case you would use the unique function.
We'll go through that and we'll talk about the differences.
So let's bring it up and just type in unique
and hit an opening parentheses
and you see how elegant this is, all it needs is the range.
So let's see if that's on my keyboard still
we'll do control + V.
Okay, so we still have that range, but we can,
if we want, we can go into this range
and include the header every single time.
So actually don't need numbers at all.
You can just say in January,
give me everything in columns A to C,
in February, do the same thing.
And in March, because the unique function's
not going to return the headers more than once.
So let's press enter and there we go.
So it returns all of the data,
but if you scroll down to the bottom,
it's actually one more in this case
and what I forgot to do, and we'll do it now,
is make some of these rows exactly the same.
So there are some repeats in this first column,
but the unique function looks at the entire range.
So let's go into January and we're going to take this row
home, 18, 206,
we're going to going to paste it in March also.
All of the functions will update.
This is just a formatting change.
That's the same as the ones above it.
So all of the functions will update automatically,
but this one should have gotten one shorter, yes.
So it's the same number of rows right now,
but it has a unique record in it that it got rid of.
So you're maybe wondering why it's the same number of rows
and it's because it takes the first blank row
and returns that as well.
So that's typically not what you want, right?
So you got rid of the unique values,
but it put a blank row in there.
So the last technique that we're going to use
is going to deal with that blank row.
So let's go one to the right again.
And in this case, what we're going to do
is use the unique function again,
but then nest the sort function inside of it.
So let's go grab the sort function that we built,
all right, we'll copy this.
I don't want the equal sign
because the equal sign is going to be
a little bit outside of it.
We'll go one below 'cause I think I need to do
the header manually.
I'm going to hit F two to put my cursor into the cell,
I'll type the equal and then I said,
I wanted to nest the sort function inside of unique.
So we'll do unique
and then we'll put the sort function inside of it
and we'll close it off and hit enter.
And what this does is that it sorts the output
so you'll see it put everything in order.
And then when you get to the bottom,
that blank row is really here,
but it doesn't matter because it doesn't show up.
So we'll grab the header let's just grab it from March,
copy this over
and you have your fourth and final way to do this.
And this is the way to return sorted data,
unique data with no blanks.
So the last thing that we want to do,
let's say we have a file
with second quarter data in it as well
and we want to join that to this tab called quarter one.
So, keep doing that, so let's add another sheet,
we'll call it quarter two, no, we'll call it year to date.
So it'll have one and two combined.
Year to date.
And we're going to grab quarter one
and then import quarter two.
So let's do this by using,
let's just do it the first way with the filter.
Let's do the unique function, it's easier, cleaner.
So I'll copy that
and let's go to year to date
and we will paste that.
So, so far, this is just getting the three worksheets
that we did before.
First thing that we'll change is
we're going to keep this as an array,
but we'll say the first worksheet is quarter and one
that we've used to join things up already.
So if you want to type it in,
you do the name of the range and exclamation point
and then the coordinates of the range, we'll say,
and I don't remember what they are
so I'm going to guess at something.
Okay, think that worked.
41.
I grabbed the first range
and then it's one shorter because I used the unique function
so it didn't return that duplicate,
but I want to add something to the array here.
So I will, first, let's go to the right,
so I can do this function.
You want the function called import range.
And that just pulls in data from another file.
So the first argument that it wants is the spreadsheet URL.
Let's say we're going to use this quarter two sales.
And I just, go up into your browser bar
and you look at this address
in the docs.google.com spreadsheets
and then it has all these letters and numbers,
just take it right before it says, edit, that'll work.
So we'll copy that.
And then you need quotes here because that's a string,
not a number, paste that,
and then the second thing that it needs
is the range within that sheet.
So let's actually see what that is, I'm not sure.
We'll say it's
Q two
A-eight all the way down.
Q two exclamation point A-eight
to the end of C
we'll hit enter.
And I forgot my quotes, so let me put the quotes around it.
Usually don't need quotes around the range,
that's kind of weird.
I do that and you get this reference error at first
because you have to give it access to get that.
So you can't just reach into another file
without giving permission, it's just a security setting.
So we'll say allow access and then that function works.
So I wanted to do it outside of that longer formula
so we get the allow access out of the way,
but then let's copy this import range formula.
So we'll do control + C
and we'll go back into this unique function
and we'll specify that as the second range in this array.
Paste it in, hit enter, and let's see if that works.
So it did it really quickly.
We'll scroll down and it's longer.
So you have that blank value in there,
which we had already talked about how to deal with,
but that's how to join two tables from different files.
So we'll talk more about how to join tables if the columns
and the headers aren't exactly the same.
So if you just want to pull in data from another table
based on kind of matching some of these values up,
I'll link to that.
And if you liked that video and you want to see others
like it, please subscribe to my channel
and I'll see you in the next video, thanks.