Hello, guys, and welcome back to another class of our course with the complete introduction to the
to be with except so into this class, we are going to learn how to do another really cool thing with
basically mackerels.
And basically what we are going to learn to do is sorting our data.
So for the purpose of this course, what we are going to do at first is simply building up a database
that looks something like this.
So we can see it's a really simple database.
And it the what it does, it generates random values.
So you learn at the same time how to create a database that looks something like this.
So we'll start by what what exactly is this database?
It's houses, number of rooms in the house.
They're built number of schools near the house making if there is a match that is near and the price
of the house.
So we'll build up a database that will be based on random values and then you will be able to copy and
paste this database to be able to create a table with it.
So let's start.
All right.
So pretty simple.
First step, we are going to create a header that looks like this.
So basically, it's going to be type house condo.
So basically one or the other.
Then we will have a number of rooms.
Then we will have our year that the house have been built, number of schools, if there is a match
on here and finally the price.
So on the last step, we will have that price right then.
Right now we will use a function that is what the most used function in this table will be, the if
function.
And basically the first question that we want to ask ourselves is this house a house or a condo?
So instead of writing down the house, condo, house, condo, house, condo, what we'll do is simply
use an if function to be able to do so.
And before what we are going to ask Excel to verify if the value will be here will be zero or one or
one or two.
And if the value is one, we will have a house.
If it's zero or two, it's going to be a condo.
So we are going to write down our first function right here that's going to be in between.
So we want to generate a number that is between one and two.
Then right there we are going to write down or if function.
Pretty simple, if the value right here is equal to you want excel to generate a house.
If not, we want Excel to generate condole.
It's really important to use those parenthesis right there.
As you can see, if we verify it, it works pretty fine, right, next step, we want to have the number
of rooms.
So pretty simple.
How exactly do we know the number of rooms we are going to generate a random value between, let's say,
three and six.
So it's going to be Rende between in this case three and six year build.
So you can generate a random value between, let's say in this case, two thousand and two thousand
fifteen.
So it's going to be pretty much the same thing as this table right here.
Number of schools will generate a random number that will be between one and in this case, four, if
there is a match one year.
So basically, we want to excel to say yes or no.
So how exactly will do this by with the same thing that we did in this case right there.
So we'll just generate a random number right here that will be between one and two.
So in this case, if.
This element right here is equal to one we want Excel to generate.
Yes.
If not, we want Xcel to generate No.
Right.
So everything works fine.
And finally, the price we can generate a price between five hundred thousand and nine hundred thousand
dollars, so it's pretty good for a house.
So five hundred thousand dollars and nine hundred thousand dollars for a house.
So everything works pretty fine.
So right now, we can test everything.
Just see everything works fine.
Next step is pretty simple.
It's just putting well, first of all, take all this will drag it down there and each time refresh
the page.
As you can see, all the numbers will change.
So next step is pretty simple, is just putting everything inside of a table.
So we are going to go to insert and then we are going to select table right there.
So we'll just do it again here in Sir David.
Right.
Here we go.
All right, we'll keep it like this.
All right, so right now we have everything that we need.
So next thing they want to do is pretty simple, is just deleting those elements right here.
But we can necessarily delete them because if we delete them, we will not have random values.
So what we'll do, we'll just hide them.
So pretty simple.
We can just hide those cells.
They're right, so right now, everything works fine.
We have our table that can generate random values.
So what we can do right now with a table that looks pretty much like this will just take away.
Well, it's the same thing.
So basically, we'll work on this one because it's way bigger so we can delete this table right here
because you already built it.
So what exactly we can do with a table that looks something like this, well, you can do pretty much
a lot of things.
You can well, you can sort out elements that are inside of this table.
So basically you can sort your elements, buy houses and condos.
You can sort them by a number of rooms.
You can sort them by the year that the house have been built, number of schools match one year or the
price.
And basically the goal here, well, in this class is taking this sorting that you are able to do and
put it inside of a micro.
So let's say someone want to sort of.
Well, let's just go in there.
Here, take away the filters and let's say someone want to sort all those elements by the house.
So basically by the house.
So we want to sort everything by the type a house or condo.
So it's going to go he will go here and we are going to sort our elements by house work on.
And here we'll have first of all, it's still random values.
So first, the first thing I forgot to mention it, if you use random values, it's not going to work
properly.
So the first thing that we are going to do, we are going to take this table and we are going to copy
and paste it right here to not have random values.
So we'll just select everything that we have.
So the whole table will copy it and we are going to test it right here.
But it's really important to test it by using by only passing the values.
So nothing besides values.
So here we go.
We have tested our values.
Now we can basically delete what we don't.
So perfect right now.
We'll just we select everything.
And create a table.
Great.
So we have our table right now, so I repeat what I just said, so we'll hide this table right there
because we won't need it anymore.
All right, so right now we want to sort our table by houses, so how exactly we can do this?
So if we try to do it this way, we'll need to delete, conduce, and we only have houses that will
be left out.
If, for example, we only want if we want to have houses at first and then we want to have condos,
how exactly we can do this.
So pretty simple.
Just bring it back.
So you guys are going to use this element right here.
So in the data, you guys are going to use the sorting element.
So the starting right here, let's say you want to basically have houses first or condos first.
It's going to be like this.
So as you can see, you will have all the condos that will come first and then you will have all the
houses after that.
So let's say you want to sorted by type house condo, then you want to sort it by the number of rooms.
It's possible to do it as well.
And it's going to be once again by this element right here.
So you will simply add another level, then you want to sort it by the number of rooms.
So first of all, we will have condos and all the candles that have three rooms, then all the cons
to have four rooms, then all the countries that have five rooms, et cetera, until six rooms, and
then the same thing for the houses.
So basically what we want to do right now is simply say, well, basically simply putting together all
the different sorts that we want.
So basically, let's say we want to create Imakura that will sort the condos and the houses, as well
as the number of rooms in one market.
So basically we simply apply this micro wants and what's going to happen, it will automatically sort
everything how we want it to be sorted.
So exactly.
We do this.
So pretty simple.
OK, just delete my past.
Sorry.
Things so pretty simple.
What I can do right now.
Well what, how I can do it.
It's pretty simple.
So first of all, are you in the developer tub.
So in this case let's create a few macros so the first macro will sort it by type.
So basically House can do this.
Well let's say.
Yeah.
So how's the second one all sorted by your build.
So we'll keep it really, really simple.
So as always, we are going to select our relative references and we are going to record the macros.
So basically how exactly let's call it sort one.
And let's try it out, so pretty simple, we are going to go to Delta and first thing that we are going
to do, we're going to sort it by in this case.
No.
So we said typo scandal and then number of rooms, and then we just click on, OK.
So here we go.
The first sorting have been done, so when it's all done, we just go here and we stop the recording
process.
All right.
Second thing that we want to do, we want to sort our table by the number well, by the year build.
And let's say that number of schools that we have near the house, so pretty simple.
We're going to come back here.
We're going to use relative references.
We are going to record the MICU.
We'll call it sort.
Two, and let's do this, so we'll come back to that.
We'll go to sort and in this case, we are going to sort of bite your belt and then number of schools
that we have here, once again, you click on OK, and right now it works pretty fine.
So first of all, we have the year built right here and then we have the number of schools in there.
So everything works fine.
All right.
So right now we have to microbes that have been well, they have been created.
What we'll do, we'll just try those microbes out to see if they work perfectly.
So we have solved one.
We can run it.
And as you can see, everything works fine.
So we have our condos and the number of rooms that have been sorted, how we wanted them to be sorted.
And same thing for story number two so we can run it right here and same thing happens.
So we are able to sort by the year built and the number of schools in here.
So just verified.
All right.
So right now, as you can see, everything works perfectly fine right now.
So this is a thing that you guys can do.
So it's pretty cool.
Basically, if you want to store your house well, the way you want to switch your table, you can do
it as well.
And as you can see, it works pretty, pretty fine.
And that's another way that you guys can use macros.
When you are working out with the Microsoft Excel, you can use it to sort things.
And basically, instead of trying to find how exactly you have sort of different things to be able to
analyze your data, you can simply save it in your micro so you can save it as a macro and use that
for that that way.
So let's address those guys and see our next class.