William Rosener: hello, this is Dr bill welcome back and this video you'll learn about sorting in Microsoft excel along with a review of loops let's start by taking a look at what you learn how to create and then we'll go ahead and finish it.
William Rosener: So, first of all we're going to go ahead and create that some command buttons here so i've got.
William Rosener: mentioned will get 10 stores i'm allow the user to enter more here so i've got 10 stores various States cities and here are the sales, maybe for the last day of the week.
William Rosener: Okay, so i'm going to allow the user to sort by state, I can see i'm going to place this and increasing order I can price it by city.
William Rosener: Where I can sort by sales or I can also sort of the default here by store number.
William Rosener: i'm going to go ahead and that creates more given command buttons here what it's going to compute the lowest and the second one upon the lowest in the second loss sales throughout the stores.
William Rosener: they're going to compute the two highest the highest and the second highest i'm going to find the average for the Oklahoma so there's 123 currently refined the average and i'm gonna find out how many stores of sales have at least 3000 i've got one, two and three here.
William Rosener: OK, now that we know what we're going to do on let's get started here so in the same area as you found this video, you should also find a link here, so you can download the interface there.
William Rosener: let's go ahead and start off here with the store numbers or allow the user to sort by store number, so we can Double Click on the design mode and then Double Click on the store.
William Rosener: So, first of all i'm going to type in is a range and i'm a specified.
William Rosener: issue carne de to.
William Rosener: yeah TIM, let me just stop for all the sin that i'll come back and explain it nobody go a 11 to do.
William Rosener: That sort them to put an underscore continue this on the next slide and I was put a space after that.
William Rosener: Okay, why a to D to.
William Rosener: I want to start here and 82 i'm gonna go to the two and then i'm going to go start here go into a 11 de la.
William Rosener: Okay, so i've kind of specified the whole range.
William Rosener: Because I don't want to just.
William Rosener: I don't want to just sort one column, at a time, I want to make sure that when I sort all of the data is also moving with it.
William Rosener: Okay, so come on back here, moving on i'm gonna have just one key on this one will be called key one that's reserved for their equals.
William Rosener: range.
William Rosener: And i'm just gonna be putting a to hear.
William Rosener: about it, I could put this on there so i'm not going to put this on the same line here in order for one.
William Rosener: we're going to go in ascending order.
William Rosener: We put in a comma or, put another underscore and a space and they want to put header.
William Rosener: is now.
William Rosener: Okay, and notice i've got header know this reason, the reason for that is I could have specified a one through D one and then specify header is just I went ahead and started down here, so I don't have a header.
William Rosener: Okay, so there is the code that tell you what i've already think that was a nine here, initially, and I was just checking that out earlier, so if you want to.
William Rosener: Initially nothing's going to work because the thing was already an order here, but I think I change this to a zero, at which point when I sorted shirt another puts it at the top there.
William Rosener: OK now let's go ahead and sort by state.
William Rosener: Oh, before moving on, I want to come on down here right now I was to come on down and put putting down here put zero.
William Rosener: But Oklahoma.
William Rosener: And i'll put muskogee here.
William Rosener: In a few sales.
William Rosener: and evaluate that justified so kind of looks like the rather than ones there, I want to make sure that my sword is also going to accommodate this, and right now, it will not.
William Rosener: supposed to come up here to the developer tab we press on sword, it is not going to sort this last one, because I stopped the sword in at 811 okay so let's go ahead and modify that so rather than hard coding this but i'm going to do is put a range.
William Rosener: i'm going to put specify that same value there a to colon de Tu.
William Rosener: In.
William Rosener: exile down.
William Rosener: South and what that's going to do is keep searching until there's either an empty or a non filled so.
William Rosener: So in this case it's just going to keep on coming down till I got the light 13, at which point we're actually going to stop that line 12 because 13 is empty there.
William Rosener: OK, so now i'm going to press sword and Sure enough, I can see, even though, if I keep adding it's going to keep on sword in there because i'm simply going to target to a as long as there is data they're going to sort it OK now let's move on to state.
William Rosener: And to do the state i'm just going to simply grab this code.
William Rosener: and paste it, and now the only differences, I was sorting this on a two.
William Rosener: And, rather than to do that i'm gonna start on B2B.
William Rosener: Okay, go ahead and close that out.
William Rosener: press on sort by state.
William Rosener: And Sure enough, a CS and so forth there one little thing here.
William Rosener: What would be nice as possibly to sort first by State then by city, so it would be broken arrow would be at the top of this list and tell us would be at the end, since that come along and do that.
William Rosener: We come right back here, I can see them in the state and i'm going to do is add a second key.
William Rosener: And I was going to try and copy paste this copy and.
William Rosener: paste it.
William Rosener: And i'm going to add a key to.
William Rosener: And on this one i'm going to put.
William Rosener: cto so now i've got sorted, first by State then by city there OK.
William Rosener: So now, if I press.
William Rosener: And sort by state, I can see it's sorting first by State then it's also sorted by city so Oklahoma first in broken arrow is going to be telecom and toss it out okay now it's sort of just by sitting.
William Rosener: And i'm just going to grab this one right here copy ads come up here paste it.
William Rosener: And to sort by city that's going to be see to.
William Rosener: change that from an A to see.
William Rosener: Go ahead and let's check it out.
William Rosener: Okay sorry by city and that looks perfect and now we're going to sort by sales.
William Rosener: So i'll just grab.
William Rosener: This copy it.
William Rosener: paste it up here, and the only thing here is i'm not going to be looking in started in detail.
William Rosener: Okay i'm gonna go ahead and close out.
William Rosener: and start by sales there.
William Rosener: And that's looking perfect, so I can sort by store number sort by state city and sales and what you'll find is a lot of times, people will literally take these icons these buttons here.
William Rosener: And resize this.
William Rosener: Okay, and so now what would happen is i'm sure I can come over here and press sort by store now if I just press right here, I can sort by stage.
William Rosener: I sitting or by sales.
William Rosener: And I couldn't move that one over there and move it over here.
William Rosener: Okay now what we want to do is read some form or actually I won't start off, I can show you how to write a formula to compute the lowest sales order to find is there is no men to.
William Rosener: So I could use to come up here and type in men specify the rage D to I can hard code this and put the D 12.
William Rosener: And sure enough it's going to look through here and find the smallest value.
William Rosener: Okay, but what if I want to get the to find the two last ones, the lowest and the second list or maybe want to find the lowest the second list and the third laws okay that's going to get a little bit more trickier and.
William Rosener: Again, there is no Microsoft excel formula called been three, which is going to find the three low so at that point, you have to write your own can.
William Rosener: Somebody ahead and Double Click on lowest.
William Rosener: Okay, and what am I going to do here is, first of all i'm going to grab the code for sales here it is.
William Rosener: i'm going to copy this so i'm working on the lowest first of all i'm going to sort.
William Rosener: sales.
William Rosener: So I simply grab the code, I now know it's sorted by sales, I can now take this value in this value and play some here because i'm going to momentarily sort of by sales.
William Rosener: which point I know this is going to be the lowest, and this is going to be the second lowest saw need to do is i'm going to take what is in.
William Rosener: row to this is 1234 and i'm going to be placing them in seven so 1234567 is where i'll be placing the data there so i'm going to put to the.
William Rosener: cells.
William Rosener: To seven.
William Rosener: that's simply what to blitz instills to form.
William Rosener: and sells.
William Rosener: It sells.
William Rosener: For.
William Rosener: Okay, so two seven is to form.
William Rosener: So to seven is going to be what is in two, four and this one is going to be once in here Okay, and then what i'm going to do is go ahead and once i've done this i'm going to go ahead and resort them back by store number.
William Rosener: Okay, so i've sorted by sales i'm not going to go ahead and grab this card right here.
William Rosener: And i'm going to put it to a little return.
William Rosener: Back to.
William Rosener: In sorted by.
William Rosener: store number.
William Rosener: Okay, so regardless of what order, it was in i'm going to first of all sorted by sales, I can then grab the two loaves values at the top and then i'm gonna return them.
William Rosener: restart the data back again.
William Rosener: Okay, so let's go ahead and.
William Rosener: i'm going to press on sort by stores just i'm going to put these all in order here.
William Rosener: i'm now going to hit lowest sales.
William Rosener: So now i'm going to grab some kind of maybe saw that quickly it's sorted by sales placed the lowest ones up here at the very top, I then simply grab those values and place them over here, then I returned to being sort of I store number again okay let's go ahead and do highest sales.
William Rosener: So, again here for highest we could have done Max it was.
William Rosener: that'd be a two column D 12.
William Rosener: Okay, and I could find the highest sales there, but I also want to get the second highest.
William Rosener: Okay, so how am I going to do that i'm repress on highest.
William Rosener: And, first of all i'm going to come on down here to sort by sales.
William Rosener: Okay, so we're going to sort this by sales but notice right now it's placing these in ascending order.
William Rosener: And I got a few options there one day which you might be tempted to do as well, I can sort these by sales.
William Rosener: I can come down here and grab the last two well as I start to add more entries below here it's not going to be in this case row 11 column for.
William Rosener: pretty soon it's going to be for so what I want to do is i'm going to go ahead and support these indecent in order that way I know the top two are always at the very top.
William Rosener: Okay, so now we can read back here hi sales.
William Rosener: Okay.
William Rosener: And i'm gonna go ahead and sort of.
William Rosener: and decent, in order that way I know my top two values right the very top there.
William Rosener: Okay, at this point I where, am I going to store the data i'm going to be placed in this in seven so it's row for column seven and then row five columns seven is going to be equal to these two values.
William Rosener: Okay, and come back here the highest sales.
William Rosener: And i'm going to go ahead and say.
William Rosener: cells for seven.
William Rosener: is equal to cells.
William Rosener: For.
William Rosener: themselves.
William Rosener: Five seven.
William Rosener: is equal to cells three four.
William Rosener: And you can slip so.
William Rosener: You can see these last two are the same here so i'm sorting the data differently i'm going to grab that and now place it in real four and row five.
William Rosener: Okay close that out.
William Rosener: Now we're going to come right up here and i'm going to hit highest sales and sure enough i'm grabbing the two highest sales there, so what did I do ups let's go ahead and once i'm finished with this we're going to put it back to get sorted by store number.
William Rosener: Okay, so i've sorted it by sales i've grabbed him now let's come along and sort by store number, so what I can do is I can grab this code right here copy and paste it or what I could do is just simply call this routine.
William Rosener: Okay, so i'll just put a call in the cmt stores like i'll tell you what the first time, let me go ahead and paste all of the code they're going to come right down here and grab the store number grab this came up here and paste it.
William Rosener: Okay.
William Rosener: i'll run that.
William Rosener: Let me just show you here so here's the highest here's the lowest.
William Rosener: Okay, what I can do.
William Rosener: is rather than.
William Rosener: paste and all that code so i'm going by store number here here's a routine it see md underscore click I can just go ahead and.
William Rosener: So that was the lowest sales are showing the right one here here's the highest sales, they could actually do the same thing below there as well.
William Rosener: On cmt store click and if I want to jack and I could have done that awesome right here rather.
William Rosener: You really have these three lines, I would have one line, but the big thing is, if I later on start adding.
William Rosener: More columns etc I don't have to sit there and copy and paste that code because it's being taken care of in the celebrity in someone's going to simply call that server team builder while it's going to be taken care of there.
William Rosener: Okay let's check this one more time here.
William Rosener: So obviously we can sort this by state city.
William Rosener: Now I can compute the to lowest and I can compute the two highest now what we're going to do is go ahead and compute the average for Oklahoma it's like out 1234 stores, currently, and I want to compute the average there.
William Rosener: And I probably should have tell you, if you want to.
William Rosener: While you're checking this out, it might be helpful, just to put some other numbers in here.
William Rosener: And they put these in in reverse order.
William Rosener: Okay.
William Rosener: i'm just making it easier their self I choose the lowest sales sure not that's one and two, I get the highest sales it's 10 and nine.
William Rosener: And now, here, presumably compute the average of Oklahoma i'm not dealing with these big numbers here i'm going to have 10 658 10 eight.
William Rosener: Six and five, not to compete that average a little bit quicker there as opposed to trying to deal with these bigger numbers okay let's work on Oklahoma average, I want to find out what's the average sales in the state of Oklahoma.
William Rosener: Okay, and to do that i'm going to go ahead and.
William Rosener: we're going to start a little loop here in just kind of review a loop in here i'm going to put total sales.
William Rosener: store counter and all these will make sense or a little bit and then i'm gonna call real counter.
William Rosener: Okay, total sales initially is going to be zero and how many stores.
William Rosener: or store counter is going to be also zero in my role counter which Rome I going to start on i'm going to be starting on wrote to you, and why to.
William Rosener: Because i'm going to be starting right here so real one, is my header somebody started on wrote two and i'm going to be looking through all of this until I reach a non until I reach a style, that is not filled there.
William Rosener: Okay, and how am I going to do that i'm just simply go wow not.
William Rosener: This empty.
William Rosener: cells here i'm gonna put.
William Rosener: Real counter.
William Rosener: Economics come back and explain this in a second and eventually i'm gonna put a.
William Rosener: Little close in my wild up there Okay, so this is going to keep going wallet is not empty, while not so there's a routine called is empty.
William Rosener: And i'm going to put an order from that while it's not empty and we keep going so what's going to happen is i'm going to take a look at selves.
William Rosener: Initially it's two to one so i'm looking at row two column one is this thing not empty it's not so i'm gonna keep marching on down ambition i'm gonna get down to 13 and i'm going to stop here, because that case there is some there's no data there.
William Rosener: Okay i'm not going to go ahead and take a look and i'm going to go is cells.
William Rosener: or counter.
William Rosener: comma two.
William Rosener: Is that equal to.
William Rosener: Oklahoma.
William Rosener: OK so i'm going to be starting off row counter is to sort of be like into now i'm looking at road to calm to i'll be looking at row two column to.
William Rosener: Go to and then here's my to.
William Rosener: Is that equal to Oklahoma Okay, it is now i'm going to say my total.
William Rosener: sales.
William Rosener: is equal to.
William Rosener: total sales plus so mine initially total sales was are all some say in this case zero Plus, whatever data is in here, in this case it's a row to column for.
William Rosener: Okay, so i'm going to take what's in so i'm going to be going through Here are two here's the four and that's coming from right over here, so when you starting to wrote two i'll be looking at for So if I got a match to add this up so i've got it will be added up to 10 nope.
William Rosener: Okay, now I need to increment my store counter.
William Rosener: This one, so I found one store eventually in this particular data set right here i've got 1234 I need to know how many stores are in Oklahoma that way to compute the average I can simply add them all up in this case divide by four.
William Rosener: Okay, so i've got my store quick counter I can now in my if statement and now very important here, I need to make sure increment my role counter.
William Rosener: If I didn't I would get a runtime error, because it's going to literally loop over and over and over because, are we going to start off with row two.
William Rosener: Is it empty Tu Tu Tu Tu Tu Tu Tu it's never going to change, but now i'm recommend my real counter so now, if you take a look at rw 234-567-8910 eventually get here to.
William Rosener: And it's going to go, is it not empty and it's going to evaluate the faults, at which point it falls out.
William Rosener: OK, so the only the last step, we need to do is to display the result, and that is going to go in row six column seven is where I want to display the answer there.
William Rosener: And that's simply equal to the total.
William Rosener: sales.
William Rosener: divided by the store counter.
William Rosener: So.
William Rosener: If I had done, you know, a couple hundred dollars in sales i'm going to simply divide that by how many stores, there are, and in this case i'm dividing it by 1234 but again I could add additional stores here in Oklahoma and i'd wanted to work as well.
William Rosener: Okay, so let's go ahead and take a look at that.
William Rosener: And maybe I should get a calculator just to make sure here so i've got 1018.
William Rosener: And then i'm going to have plus 11.
William Rosener: divided by four and 7.25 so that's working perfectly there Okay, so at this point, let me just run through this all again, one more time i'm actually let you work on this yourself here.
William Rosener: And if you want to knowledge, you got some we checked it out on some simple numbers, I could come along.
William Rosener: paste this on back up here.
William Rosener: And let's run through this again I can sort by state Now let me move this back over here i'll leave that up to you, if you want to where you want to leave those out.
William Rosener: Okay, so I can sort by state, I can sort by city, I can sort by sales, I can then compute the lowest on the to lowest.
William Rosener: To highest I can find the average for Oklahoma and now all I want you to do is find out how many stores.
William Rosener: There are at least $3,000 in sales and like 1234 so i'll let you work with that I have, I have a feeling having gone through all this there's lots of ways, you can solve that I think you should go to handle that anyway i'll catch you next video thanks bye.