Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
Okay, This is the final example you're going to see in this module.
00:05
And the data you see represent sales
00:09
data
00:09
collected in ah when the Friendship Store,
00:14
where manager has decided to organize a sales competition. And unlike regular competitions that they're usually about who sells the most or who makes the biggest margin
00:26
here, he's going to
00:29
give
00:30
prizes to those salespeople that achieve highest discount. Sorry, Lo. Is this count or high selling price on to selected products? One type of chair in one table.
00:44
Why highest selling price? Because this is a wholesale store. So people come in and say I want to buy 20 or 50 or
00:52
100 chairs and how much this country going to give me
00:58
and they agree on a discount. And then, ah,
01:03
it gets ah,
01:04
recorded by somebody from an administration
01:07
entered in the stable
01:10
and at the end of the competition they want to extract from this data. So what you see here is just on example.
01:17
It can be hundreds and hundreds of rose.
01:21
They want to find who got the best selling price high selling price for chair and high saying place for table.
01:29
If there are more sales people who
01:32
ah, get go to the same selling price for
01:36
either of these items. They will simply share the
01:40
cries, which is in money.
01:42
So,
01:45
um, it's very simple. Data is quite clear. Have sales person.
01:49
We have product that is selected from the dropdown list.
01:53
Then the list price is calculated automatically because we know it's 30
01:57
for
01:59
a chair and 85 table discount is entered by
02:04
the administration person
02:07
and price at which the product is sold has been calculated automatically with this simple formula.
02:15
So because we can have more than one,
02:20
um,
02:21
person achieving the best selling price for chair
02:24
and for table, there can be more than one entry here, and Manager wants all of them listed here
02:34
in
02:36
these separate comes for chair and table,
02:38
and he was just a click on this button
02:42
and get the results so
02:44
she wants to do it in front of
02:46
whole team.
02:47
So,
02:49
uh, we cannot use look up functions. We have to write the macro
02:53
and macro. I'm going to write for this
02:57
Ah,
02:59
competition.
03:00
I have nothing prepared, so I'm going to write it from scratch. And if I make some mistakes.
03:07
Uh, it's normal.
03:08
Even if you're really experienced, you probably make some mixed aches, even or or it can be typing or even in some logic, and then you have to correct it. So let's ah
03:22
not expect that this will go without any hiccups.
03:25
Um, if he does perfectly, but I want you to see how the process of creation looks.
03:32
So let's go to Mac Rose. Let's enter
03:36
the name of the macro is going to be winners
03:39
and let's create it.
03:43
And we get with open,
03:46
empty sheets
03:49
so we're first have to define our variables.
03:53
So we're going to have first,
03:57
um,
03:59
makes selling
04:02
or, let's say, just mix
04:04
price for chair
04:09
and well defined it. This Ah,
04:12
this symbol number so single
04:16
and then
04:20
makes
04:23
price
04:25
table,
04:26
also as
04:29
zul.
04:30
Then we're going to need some counters
04:33
and let's not give them any complicated names.
04:39
So let's get say we're going to have a counter that was going to go through cells is going to be a rosary is going to be I.
04:46
So it's gravity in future,
04:50
and then we're going to have a counter for,
04:56
um,
04:58
how many sales people have reached the
05:01
maximum selling price for boat.
05:04
So is goingto this
05:06
Martian
05:09
and it's going to say,
05:11
Ah,
05:12
sales.
05:16
I would just say sales counter
05:19
share
05:23
is
05:24
into jer
05:28
and
05:32
table also his integer.
05:36
So this one is basically going to count how many people
05:41
are
05:42
achieving the best price
05:45
so that we can,
05:47
uh, ads or move our table one row lower when we put their name there. So it's also going to be integer,
06:00
and
06:02
that's pretty much it at the beginning. If we find out that we need some
06:08
other variable will define it later, doesn't
06:12
intrude with the
06:14
writing off the code itself.
06:16
So what is the first thing we do?
06:20
Let's find the maximum price for chair and maximum price for table.
06:27
So it's going to be do loop, and this is going to be do look, until so we're going to go through all the items until we reach that there are no more entry. So that's an empty
06:39
field,
06:41
and
06:42
what are we going to do
06:44
is
06:46
we're going to ask
06:46
first of all, yeah, before that,
06:49
we're going to set maximum selling prices
06:55
for chair and for table 20
06:59
because this is definitely a minimum and everything has to be
07:02
hired and it's so it's zero
07:06
and also
07:09
for table.
07:11
It's zero
07:15
and let's start with the
07:17
questions.
07:18
So we're going to say if
07:24
and then let's look here.
07:28
So we're going to look a TTE
07:30
Ah,
07:31
this price here.
07:33
So it's called E and Colby
07:40
and of course, yeah, the counter.
07:43
We're going to
07:45
Sorry
07:46
this happens when you don't finish line.
07:48
So the counter
07:50
is going to be set at
07:54
two
07:57
and then we're going to see
07:59
if
08:01
so, we're looking at column E
08:03
and it's going to be a range off
08:09
e
08:09
and I
08:13
So we're going to start with E too.
08:16
And
08:18
you have a typo
08:20
is greater
08:22
then,
08:24
Max Price share. Sorry. First we're going to look if
08:31
range
08:33
off
08:35
be
08:39
and
08:41
I
08:45
is equal
08:48
share,
08:50
then
08:54
If
08:56
range
08:58
e off e and I is higher,
09:01
then Max Priest chair,
09:05
then
09:13
next price chair
09:16
is going to be
09:20
equal
09:22
to this number.
09:24
So it's the newest highest number for
09:28
prices of chairs
09:31
we can put here that well, you a cz well,
09:37
and here we can put it
09:39
well, you, But we don't have to because It's a default value or default extension,
09:45
but let's make it
09:50
100%.
09:52
So here after then
09:54
we're going to
09:58
do this
09:58
and we're going to do, and if
10:03
and again. And
10:05
if
10:07
so,
10:09
we checked this one
10:11
and then we're going to
10:13
copied the scene,
10:15
think again.
10:18
And
10:20
then here is going to be table.
10:24
And
10:28
here we're going to have express for table.
10:33
And here is well, is going to be next press for
10:37
table
10:41
and everything else is the same. So we're looking for the
10:46
price. So if we are looking, if B
10:50
two is equal to table,
10:52
then if
10:54
he
10:56
two is bigger than Max prize stable, then express table becomes this. So
11:03
we close it
11:05
and then we do
11:07
one
11:09
loop
11:11
until
11:13
and then we're looking at the first column. So range
11:18
off
11:22
A
11:22
and
11:24
I
11:26
is equal to nothing.
11:31
And here we just forget to do one thing
11:35
when to say I is equal, I plus
11:37
one.
11:39
So we're adding one to the counter and we're looking gift. The next row is empty. If not, we go back and do it all again,
11:48
and we have finished this first loop
11:52
and let's continue in next video

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor