Alright, if you're using Google sheets
and you have one column of data,
but you want to get it in the three columns.
Okay so all of these words belong in one column,
the numbers belong in two others.
And let me expand what I've hidden here in let's say
this is how you want it to work out, these three columns.
So you want to essentially unstack what's in column A.
We're going to go over two different ways to do it.
This is going to be one way in these columns C, D and E,
and then G, H, and I is going to be another way,
so the first way that we're going to do it,
we'll just dig right into it.
And this is kind of a hack it's kind of a work around,
but you'll see why.
So if you were to do this manually,
we'll talk quickly about why it doesn't work.
So in cell C3, you want the value in A1 right?
So if you go here and you grab A1
And then in cell D3 you want A2 and so on.
Everything's fine so far, right?
And you could manually do all of this, right?
But let's say you have a really long column
and you're looking for a shortcut.
The problem is when you select these three
and you go to lower right hand corner,
and you left click on the square,
when you drag this down, Google sheets is smart enough
to know that it should increment it,
but it won't increment it correctly.
I'll show you what it means so I'll drag this down
and I'm going to go up to View and to Show formulas.
And what this did if we just look in column C
is incremented everything by one.
So just want A2 and A3, and here it did the same,
but it needs to be incriminating by three.
And there's no way to do that with the Autofill.
So let's go back up to View, turn off Show formulas.
We're going to delete these
and I'll show you the work around now so we're going to
also delete the first row of formulas
and we're going to use filler text.
So we'll just say filler A1.
Here it's going to be filler A4.
So we'll repeat this process for the other columns
in A6.
Now the Google sheets, doesn't see these as cell references
it can guess more accurately what you wanna do.
So it knows these are incremented by three.
When you drag it down, let's just go down
further than we need to.
This actually works, right?
So it has incremented the numbers.
And now let's select this area, we're going to do
Ctrl+H as a shortcut, or actually we can just do
edit, find and replace.
We want to find the word filler
and we want to replace it with an equal sign.
So that's going to change these into formulas,
so left click on replace all and there we go.
It replaced all the words filler with an equal sign.
So Google sheets turn that back in those cell references
and it's incremented correctly.
I purposely went down a little bit further than you need to.
And that way, when you add something at the bottom,
I mean, it's going to show up here.
So that's the probably conceptually easiest way to do this.
And if that's all you need to do, then you're done.
But if you want a way to do it just with the function
and none of the workarounds and the find and replace,
we're going to do that here.
So let's go ahead and hide this first attempt.
So you notice when I typed in table at the bottom here,
it also filled into this field, but if I hit the delete key,
it doesn't go anywhere because all of this data
is being produced at the top so if I go into G3,
this is where our formula is.
And likewise, if I go into H3 and I3,
so we're going to talk about this formula.
It's a kind of a string of functions.
They're nested inside of each other,
but in plain English, what this is doing is it's saying,
look in this range and that's what A1 to A is.
So there's no any number on A, and that's a shortcut
to be able to select the entire column starting at row one.
So the start A2, you would just change this to A2,
but this goes all the way down.
So it's going to find any new values that you add.
And the filter function looks at a range
and it only returns what makes it through the filter, okay.
So it's going to return everything
that makes it through the filter though.
So that's why all of this data is here,
but it still just lives in G3.
So everything below, like I showed you before and even G4,
it can't get rid of it, it's being dynamically written,
but let's go back into the formula.
The filter function is saying, give me some data,
tell me how to filter it out
and I will only return what's left.
So this is the data, A1 to A, and then we get
into the equation and what this equation is doing is
it's taking the row number and then adding
the different amount based on which column you are
and dividing by three.
And then when there's no remainder, it returns a value.
So that's getting every third value.
And then we're controlling where it starts
by this plus two on this formula,
plus one here, this exact same formula,
except for these differences and plus nothing here.
So this equals zero is saying, there's no remainder.
So Mod looks for a remainder.
This row returns a row number.
This three says divide by three and this says,
do it when there's no remainder.
So that's all you do, you put these in the first row
and they'll return as many values as you need to.
So again, if I want to add another quantity
and I go down here and I type a seven,
that's going to show up in that table.
So next, let's take a closer look at the filter function
with this video that's coming up.