1
00:00:01,640 --> 00:00:06,770
Hello, guys, and welcome back to another class of our course with the complete introduction to the

2
00:00:06,770 --> 00:00:14,760
to be with except so into this class, we are going to learn how to do another really cool thing with

3
00:00:15,110 --> 00:00:16,520
basically mackerels.

4
00:00:16,910 --> 00:00:21,120
And basically what we are going to learn to do is sorting our data.

5
00:00:21,530 --> 00:00:26,420
So for the purpose of this course, what we are going to do at first is simply building up a database

6
00:00:26,420 --> 00:00:28,310
that looks something like this.

7
00:00:28,310 --> 00:00:30,350
So we can see it's a really simple database.

8
00:00:30,920 --> 00:00:35,020
And it the what it does, it generates random values.

9
00:00:35,990 --> 00:00:39,440
So you learn at the same time how to create a database that looks something like this.

10
00:00:39,980 --> 00:00:44,290
So we'll start by what what exactly is this database?

11
00:00:44,300 --> 00:00:46,370
It's houses, number of rooms in the house.

12
00:00:46,370 --> 00:00:52,100
They're built number of schools near the house making if there is a match that is near and the price

13
00:00:52,100 --> 00:00:52,630
of the house.

14
00:00:53,000 --> 00:00:58,400
So we'll build up a database that will be based on random values and then you will be able to copy and

15
00:00:58,400 --> 00:01:01,580
paste this database to be able to create a table with it.

16
00:01:02,150 --> 00:01:03,320
So let's start.

17
00:01:03,440 --> 00:01:03,840
All right.

18
00:01:03,860 --> 00:01:04,880
So pretty simple.

19
00:01:04,880 --> 00:01:08,320
First step, we are going to create a header that looks like this.

20
00:01:08,340 --> 00:01:13,120
So basically, it's going to be type house condo.

21
00:01:14,540 --> 00:01:16,820
So basically one or the other.

22
00:01:18,650 --> 00:01:20,810
Then we will have a number of rooms.

23
00:01:23,990 --> 00:01:33,940
Then we will have our year that the house have been built, number of schools, if there is a match

24
00:01:33,950 --> 00:01:37,610
on here and finally the price.

25
00:01:40,220 --> 00:01:48,190
So on the last step, we will have that price right then.

26
00:01:48,200 --> 00:01:53,930
Right now we will use a function that is what the most used function in this table will be, the if

27
00:01:54,050 --> 00:01:54,560
function.

28
00:01:54,980 --> 00:02:01,300
And basically the first question that we want to ask ourselves is this house a house or a condo?

29
00:02:01,310 --> 00:02:05,540
So instead of writing down the house, condo, house, condo, house, condo, what we'll do is simply

30
00:02:05,540 --> 00:02:07,720
use an if function to be able to do so.

31
00:02:08,510 --> 00:02:14,810
And before what we are going to ask Excel to verify if the value will be here will be zero or one or

32
00:02:14,810 --> 00:02:15,770
one or two.

33
00:02:16,130 --> 00:02:19,340
And if the value is one, we will have a house.

34
00:02:19,340 --> 00:02:22,360
If it's zero or two, it's going to be a condo.

35
00:02:22,820 --> 00:02:27,860
So we are going to write down our first function right here that's going to be in between.

36
00:02:28,280 --> 00:02:31,430
So we want to generate a number that is between one and two.

37
00:02:33,170 --> 00:02:36,730
Then right there we are going to write down or if function.

38
00:02:36,980 --> 00:02:47,270
Pretty simple, if the value right here is equal to you want excel to generate a house.

39
00:02:49,320 --> 00:02:53,870
If not, we want Excel to generate condole.

40
00:02:57,320 --> 00:03:00,380
It's really important to use those parenthesis right there.

41
00:03:04,300 --> 00:03:09,940
As you can see, if we verify it, it works pretty fine, right, next step, we want to have the number

42
00:03:09,940 --> 00:03:10,830
of rooms.

43
00:03:11,230 --> 00:03:12,310
So pretty simple.

44
00:03:12,460 --> 00:03:18,460
How exactly do we know the number of rooms we are going to generate a random value between, let's say,

45
00:03:18,460 --> 00:03:19,750
three and six.

46
00:03:19,930 --> 00:03:27,750
So it's going to be Rende between in this case three and six year build.

47
00:03:27,760 --> 00:03:35,080
So you can generate a random value between, let's say in this case, two thousand and two thousand

48
00:03:35,080 --> 00:03:35,590
fifteen.

49
00:03:35,860 --> 00:03:38,520
So it's going to be pretty much the same thing as this table right here.

50
00:03:39,130 --> 00:03:48,640
Number of schools will generate a random number that will be between one and in this case, four, if

51
00:03:48,640 --> 00:03:49,640
there is a match one year.

52
00:03:49,660 --> 00:03:54,220
So basically, we want to excel to say yes or no.

53
00:03:54,250 --> 00:03:59,920
So how exactly will do this by with the same thing that we did in this case right there.

54
00:04:00,520 --> 00:04:06,950
So we'll just generate a random number right here that will be between one and two.

55
00:04:08,350 --> 00:04:09,970
So in this case, if.

56
00:04:12,750 --> 00:04:18,990
This element right here is equal to one we want Excel to generate.

57
00:04:19,020 --> 00:04:19,500
Yes.

58
00:04:20,500 --> 00:04:23,080
If not, we want Xcel to generate No.

59
00:04:27,960 --> 00:04:28,250
Right.

60
00:04:28,780 --> 00:04:29,690
So everything works fine.

61
00:04:29,710 --> 00:04:36,880
And finally, the price we can generate a price between five hundred thousand and nine hundred thousand

62
00:04:36,880 --> 00:04:39,410
dollars, so it's pretty good for a house.

63
00:04:40,420 --> 00:04:45,520
So five hundred thousand dollars and nine hundred thousand dollars for a house.

64
00:04:46,600 --> 00:04:48,110
So everything works pretty fine.

65
00:04:48,550 --> 00:04:50,020
So right now, we can test everything.

66
00:04:51,100 --> 00:04:52,420
Just see everything works fine.

67
00:04:52,990 --> 00:04:54,640
Next step is pretty simple.

68
00:04:54,640 --> 00:05:01,300
It's just putting well, first of all, take all this will drag it down there and each time refresh

69
00:05:01,300 --> 00:05:01,720
the page.

70
00:05:01,720 --> 00:05:03,770
As you can see, all the numbers will change.

71
00:05:03,910 --> 00:05:07,680
So next step is pretty simple, is just putting everything inside of a table.

72
00:05:07,690 --> 00:05:13,090
So we are going to go to insert and then we are going to select table right there.

73
00:05:15,700 --> 00:05:20,080
So we'll just do it again here in Sir David.

74
00:05:26,310 --> 00:05:26,900
Right.

75
00:05:34,000 --> 00:05:34,830
Here we go.

76
00:05:40,120 --> 00:05:41,320
All right, we'll keep it like this.

77
00:05:58,240 --> 00:06:00,500
All right, so right now we have everything that we need.

78
00:06:00,850 --> 00:06:05,290
So next thing they want to do is pretty simple, is just deleting those elements right here.

79
00:06:05,300 --> 00:06:09,050
But we can necessarily delete them because if we delete them, we will not have random values.

80
00:06:09,190 --> 00:06:12,260
So what we'll do, we'll just hide them.

81
00:06:12,280 --> 00:06:12,970
So pretty simple.

82
00:06:12,970 --> 00:06:15,010
We can just hide those cells.

83
00:06:17,290 --> 00:06:20,230
They're right, so right now, everything works fine.

84
00:06:20,260 --> 00:06:24,700
We have our table that can generate random values.

85
00:06:25,060 --> 00:06:30,160
So what we can do right now with a table that looks pretty much like this will just take away.

86
00:06:30,270 --> 00:06:32,420
Well, it's the same thing.

87
00:06:32,860 --> 00:06:38,620
So basically, we'll work on this one because it's way bigger so we can delete this table right here

88
00:06:38,650 --> 00:06:39,820
because you already built it.

89
00:06:42,710 --> 00:06:47,870
So what exactly we can do with a table that looks something like this, well, you can do pretty much

90
00:06:47,870 --> 00:06:48,500
a lot of things.

91
00:06:48,500 --> 00:06:51,860
You can well, you can sort out elements that are inside of this table.

92
00:06:51,860 --> 00:06:55,610
So basically you can sort your elements, buy houses and condos.

93
00:06:55,610 --> 00:06:57,380
You can sort them by a number of rooms.

94
00:06:57,740 --> 00:07:02,990
You can sort them by the year that the house have been built, number of schools match one year or the

95
00:07:02,990 --> 00:07:03,500
price.

96
00:07:03,950 --> 00:07:11,450
And basically the goal here, well, in this class is taking this sorting that you are able to do and

97
00:07:11,450 --> 00:07:13,070
put it inside of a micro.

98
00:07:13,080 --> 00:07:15,140
So let's say someone want to sort of.

99
00:07:16,100 --> 00:07:18,930
Well, let's just go in there.

100
00:07:22,500 --> 00:07:29,070
Here, take away the filters and let's say someone want to sort all those elements by the house.

101
00:07:29,090 --> 00:07:30,740
So basically by the house.

102
00:07:30,780 --> 00:07:34,180
So we want to sort everything by the type a house or condo.

103
00:07:34,640 --> 00:07:40,420
So it's going to go he will go here and we are going to sort our elements by house work on.

104
00:07:40,790 --> 00:07:44,760
And here we'll have first of all, it's still random values.

105
00:07:44,780 --> 00:07:50,090
So first, the first thing I forgot to mention it, if you use random values, it's not going to work

106
00:07:50,090 --> 00:07:50,510
properly.

107
00:07:50,520 --> 00:07:55,220
So the first thing that we are going to do, we are going to take this table and we are going to copy

108
00:07:55,220 --> 00:07:57,920
and paste it right here to not have random values.

109
00:07:58,910 --> 00:08:01,130
So we'll just select everything that we have.

110
00:08:01,160 --> 00:08:07,750
So the whole table will copy it and we are going to test it right here.

111
00:08:08,120 --> 00:08:13,830
But it's really important to test it by using by only passing the values.

112
00:08:13,850 --> 00:08:16,410
So nothing besides values.

113
00:08:17,240 --> 00:08:18,080
So here we go.

114
00:08:18,080 --> 00:08:19,540
We have tested our values.

115
00:08:20,660 --> 00:08:24,590
Now we can basically delete what we don't.

116
00:08:24,860 --> 00:08:26,120
So perfect right now.

117
00:08:26,120 --> 00:08:27,620
We'll just we select everything.

118
00:08:30,630 --> 00:08:32,250
And create a table.

119
00:08:37,210 --> 00:08:37,490
Great.

120
00:08:37,810 --> 00:08:43,510
So we have our table right now, so I repeat what I just said, so we'll hide this table right there

121
00:08:43,510 --> 00:08:44,980
because we won't need it anymore.

122
00:08:47,400 --> 00:08:52,950
All right, so right now we want to sort our table by houses, so how exactly we can do this?

123
00:08:53,340 --> 00:08:58,170
So if we try to do it this way, we'll need to delete, conduce, and we only have houses that will

124
00:08:58,170 --> 00:08:59,230
be left out.

125
00:08:59,430 --> 00:09:03,810
If, for example, we only want if we want to have houses at first and then we want to have condos,

126
00:09:04,110 --> 00:09:05,880
how exactly we can do this.

127
00:09:07,050 --> 00:09:08,070
So pretty simple.

128
00:09:08,640 --> 00:09:09,890
Just bring it back.

129
00:09:11,070 --> 00:09:14,530
So you guys are going to use this element right here.

130
00:09:14,550 --> 00:09:18,410
So in the data, you guys are going to use the sorting element.

131
00:09:18,540 --> 00:09:24,810
So the starting right here, let's say you want to basically have houses first or condos first.

132
00:09:25,170 --> 00:09:26,490
It's going to be like this.

133
00:09:26,670 --> 00:09:30,720
So as you can see, you will have all the condos that will come first and then you will have all the

134
00:09:30,720 --> 00:09:32,000
houses after that.

135
00:09:32,280 --> 00:09:37,680
So let's say you want to sorted by type house condo, then you want to sort it by the number of rooms.

136
00:09:38,640 --> 00:09:40,180
It's possible to do it as well.

137
00:09:40,200 --> 00:09:43,940
And it's going to be once again by this element right here.

138
00:09:43,950 --> 00:09:47,590
So you will simply add another level, then you want to sort it by the number of rooms.

139
00:09:47,610 --> 00:09:52,830
So first of all, we will have condos and all the candles that have three rooms, then all the cons

140
00:09:52,830 --> 00:09:57,780
to have four rooms, then all the countries that have five rooms, et cetera, until six rooms, and

141
00:09:57,780 --> 00:09:59,310
then the same thing for the houses.

142
00:10:00,450 --> 00:10:07,500
So basically what we want to do right now is simply say, well, basically simply putting together all

143
00:10:07,500 --> 00:10:09,160
the different sorts that we want.

144
00:10:09,180 --> 00:10:15,450
So basically, let's say we want to create Imakura that will sort the condos and the houses, as well

145
00:10:15,450 --> 00:10:17,600
as the number of rooms in one market.

146
00:10:17,640 --> 00:10:22,620
So basically we simply apply this micro wants and what's going to happen, it will automatically sort

147
00:10:22,620 --> 00:10:25,100
everything how we want it to be sorted.

148
00:10:26,040 --> 00:10:26,670
So exactly.

149
00:10:26,680 --> 00:10:27,080
We do this.

150
00:10:27,390 --> 00:10:28,060
So pretty simple.

151
00:10:28,060 --> 00:10:30,420
OK, just delete my past.

152
00:10:30,840 --> 00:10:31,190
Sorry.

153
00:10:31,430 --> 00:10:31,670
Sorry.

154
00:10:31,770 --> 00:10:34,650
Things so pretty simple.

155
00:10:36,250 --> 00:10:37,400
What I can do right now.

156
00:10:37,410 --> 00:10:38,910
Well what, how I can do it.

157
00:10:39,870 --> 00:10:40,560
It's pretty simple.

158
00:10:40,590 --> 00:10:42,340
So first of all, are you in the developer tub.

159
00:10:42,660 --> 00:10:48,570
So in this case let's create a few macros so the first macro will sort it by type.

160
00:10:48,600 --> 00:10:50,780
So basically House can do this.

161
00:10:50,790 --> 00:10:51,650
Well let's say.

162
00:10:51,780 --> 00:10:52,190
Yeah.

163
00:10:52,200 --> 00:10:55,410
So how's the second one all sorted by your build.

164
00:10:55,600 --> 00:10:57,510
So we'll keep it really, really simple.

165
00:10:59,040 --> 00:11:05,760
So as always, we are going to select our relative references and we are going to record the macros.

166
00:11:06,630 --> 00:11:10,680
So basically how exactly let's call it sort one.

167
00:11:13,060 --> 00:11:18,790
And let's try it out, so pretty simple, we are going to go to Delta and first thing that we are going

168
00:11:18,790 --> 00:11:22,210
to do, we're going to sort it by in this case.

169
00:11:23,990 --> 00:11:24,520
No.

170
00:11:24,560 --> 00:11:30,020
So we said typo scandal and then number of rooms, and then we just click on, OK.

171
00:11:31,710 --> 00:11:32,440
So here we go.

172
00:11:32,640 --> 00:11:38,100
The first sorting have been done, so when it's all done, we just go here and we stop the recording

173
00:11:38,100 --> 00:11:38,600
process.

174
00:11:40,020 --> 00:11:40,380
All right.

175
00:11:40,800 --> 00:11:47,600
Second thing that we want to do, we want to sort our table by the number well, by the year build.

176
00:11:48,030 --> 00:11:53,850
And let's say that number of schools that we have near the house, so pretty simple.

177
00:11:54,250 --> 00:11:56,820
We're going to come back here.

178
00:11:56,830 --> 00:11:58,520
We're going to use relative references.

179
00:11:58,890 --> 00:12:00,930
We are going to record the MICU.

180
00:12:00,930 --> 00:12:02,340
We'll call it sort.

181
00:12:03,260 --> 00:12:07,460
Two, and let's do this, so we'll come back to that.

182
00:12:08,060 --> 00:12:16,430
We'll go to sort and in this case, we are going to sort of bite your belt and then number of schools

183
00:12:16,430 --> 00:12:22,550
that we have here, once again, you click on OK, and right now it works pretty fine.

184
00:12:22,580 --> 00:12:27,690
So first of all, we have the year built right here and then we have the number of schools in there.

185
00:12:28,790 --> 00:12:29,880
So everything works fine.

186
00:12:30,140 --> 00:12:30,430
All right.

187
00:12:30,430 --> 00:12:34,660
So right now we have to microbes that have been well, they have been created.

188
00:12:35,000 --> 00:12:38,560
What we'll do, we'll just try those microbes out to see if they work perfectly.

189
00:12:38,960 --> 00:12:40,060
So we have solved one.

190
00:12:40,070 --> 00:12:40,850
We can run it.

191
00:12:40,850 --> 00:12:42,400
And as you can see, everything works fine.

192
00:12:42,410 --> 00:12:48,010
So we have our condos and the number of rooms that have been sorted, how we wanted them to be sorted.

193
00:12:48,020 --> 00:12:52,630
And same thing for story number two so we can run it right here and same thing happens.

194
00:12:53,000 --> 00:12:58,340
So we are able to sort by the year built and the number of schools in here.

195
00:12:59,510 --> 00:13:01,160
So just verified.

196
00:13:01,790 --> 00:13:02,170
All right.

197
00:13:02,180 --> 00:13:07,090
So right now, as you can see, everything works perfectly fine right now.

198
00:13:07,100 --> 00:13:09,890
So this is a thing that you guys can do.

199
00:13:10,550 --> 00:13:11,330
So it's pretty cool.

200
00:13:11,510 --> 00:13:17,210
Basically, if you want to store your house well, the way you want to switch your table, you can do

201
00:13:17,210 --> 00:13:17,960
it as well.

202
00:13:17,960 --> 00:13:20,770
And as you can see, it works pretty, pretty fine.

203
00:13:20,780 --> 00:13:23,270
And that's another way that you guys can use macros.

204
00:13:23,870 --> 00:13:28,610
When you are working out with the Microsoft Excel, you can use it to sort things.

205
00:13:28,970 --> 00:13:34,460
And basically, instead of trying to find how exactly you have sort of different things to be able to

206
00:13:34,460 --> 00:13:40,760
analyze your data, you can simply save it in your micro so you can save it as a macro and use that

207
00:13:40,760 --> 00:13:42,040
for that that way.

208
00:13:42,830 --> 00:13:46,190
So let's address those guys and see our next class.