Okay, This is the final example you're going to see in this module.
And the data you see represent sales
collected in ah when the Friendship Store,
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
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.
Why highest selling price? Because this is a wholesale store. So people come in and say I want to buy 20 or 50 or
100 chairs and how much this country going to give me
and they agree on a discount. And then, ah,
recorded by somebody from an administration
entered in the stable
and at the end of the competition they want to extract from this data. So what you see here is just on example.
It can be hundreds and hundreds of rose.
They want to find who got the best selling price high selling price for chair and high saying place for table.
If there are more sales people who
ah, get go to the same selling price for
either of these items. They will simply share the
cries, which is in money.
um, it's very simple. Data is quite clear. Have sales person.
We have product that is selected from the dropdown list.
Then the list price is calculated automatically because we know it's 30
a chair and 85 table discount is entered by
the administration person
and price at which the product is sold has been calculated automatically with this simple formula.
So because we can have more than one,
person achieving the best selling price for chair
and for table, there can be more than one entry here, and Manager wants all of them listed here
these separate comes for chair and table,
and he was just a click on this button
and get the results so
she wants to do it in front of
uh, we cannot use look up functions. We have to write the macro
and macro. I'm going to write for this
I have nothing prepared, so I'm going to write it from scratch. And if I make some mistakes.
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
not expect that this will go without any hiccups.
Um, if he does perfectly, but I want you to see how the process of creation looks.
So let's go to Mac Rose. Let's enter
the name of the macro is going to be winners
and let's create it.
And we get with open,
so we're first have to define our variables.
So we're going to have first,
or, let's say, just mix
and well defined it. This Ah,
this symbol number so single
Then we're going to need some counters
and let's not give them any complicated names.
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.
So it's gravity in future,
and then we're going to have a counter for,
how many sales people have reached the
maximum selling price for boat.
and it's going to say,
I would just say sales counter
table also his integer.
So this one is basically going to count how many people
achieving the best price
uh, ads or move our table one row lower when we put their name there. So it's also going to be integer,
that's pretty much it at the beginning. If we find out that we need some
other variable will define it later, doesn't
writing off the code itself.
So what is the first thing we do?
Let's find the maximum price for chair and maximum price for table.
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
what are we going to do
first of all, yeah, before that,
we're going to set maximum selling prices
for chair and for table 20
because this is definitely a minimum and everything has to be
hired and it's so it's zero
and let's start with the
So we're going to say if
and then let's look here.
So we're going to look a TTE
So it's called E and Colby
and of course, yeah, the counter.
this happens when you don't finish line.
is going to be set at
and then we're going to see
so, we're looking at column E
and it's going to be a range off
So we're going to start with E too.
Max Price share. Sorry. First we're going to look if
e off e and I is higher,
then Max Priest chair,
So it's the newest highest number for
we can put here that well, you a cz well,
and here we can put it
well, you, But we don't have to because It's a default value or default extension,
and we're going to do, and if
and then we're going to
then here is going to be table.
here we're going to have express for table.
And here is well, is going to be next press for
and everything else is the same. So we're looking for the
price. So if we are looking, if B
two is equal to table,
two is bigger than Max prize stable, then express table becomes this. So
and then we're looking at the first column. So range
is equal to nothing.
And here we just forget to do one thing
when to say I is equal, I plus
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,
and we have finished this first loop
and let's continue in next video