WEBVTT 1 00:00:01.680 --> 00:00:10.950 William Rosener: hello, this is Dr rosen or welcome back and this instructional video you'll learn about sorting with Microsoft excel using bba visual basic for the applications. 2 00:00:11.370 --> 00:00:18.180 William Rosener: you'll learn how to use the bba assortment that allows you to set a single key calm or multiple keys multiple columns to sort. 3 00:00:18.539 --> 00:00:28.020 William Rosener: You can define the order and ascending or descending and you can specify, whether or not you have a header let's start by taking a look at what you learn how to create and then we'll get started here. 4 00:00:28.560 --> 00:00:41.460 William Rosener: So I mentioned we've got some stores Paris countries cities and prophets I can now come over here and press on country to sort these by country, I could price on this sort by sitting. 5 00:00:42.750 --> 00:00:47.010 William Rosener: I could press on the sort by profit or by store number. 6 00:00:48.270 --> 00:00:55.710 William Rosener: Then we're going to go ahead and I created man Ben which will extract the to lowest profits and the two high profit highest profits. 7 00:00:56.310 --> 00:01:03.930 William Rosener: If I just wanted to get the lowest I can use the men, if I wanted to get the highest I can just imagine it would be grabbing the two highest and the two loves them. 8 00:01:04.380 --> 00:01:12.600 William Rosener: Okay, now that we know what wouldn't do let's get started here so in the same area as you found the link to this video, you should also find. 9 00:01:13.740 --> 00:01:20.850 William Rosener: A file here called and student 12 students that xml has in this is a macro enabled documents, so if you want to say this. 10 00:01:21.480 --> 00:01:36.600 William Rosener: rename this or We say this, and some other format, make sure it ends with an m X l m so that otherwise you're going to lose your pb I can Okay, the very first step, I want to do is get my developer tab visible to do that i'm gonna come to file options. 11 00:01:37.650 --> 00:01:43.200 William Rosener: i'm gonna choose customized ribbon and i'm going to choose developer tab and press OK. 12 00:01:44.310 --> 00:01:50.220 William Rosener: i've now got my developer tab visible, and I can, if I press right now, but they went. 13 00:01:50.730 --> 00:01:58.650 William Rosener: For dude go ahead and check your code here is, it should work here, I could sort by country and storm so i've got given me the code for these two. 14 00:01:59.010 --> 00:02:08.340 William Rosener: Other me go ahead and i'm walking through city and profit, so now i'm going to go ahead and turn off so now, when I turn off the design mode or actually turn it on so now, when I. 15 00:02:08.730 --> 00:02:20.550 William Rosener: press on one of these is going to take me to my source code, but first I need to create a couple commandments i'm just going to highlight this one, it copied control C control V to paste copy paste now. 16 00:02:22.050 --> 00:02:30.510 William Rosener: And then actual pistol twice and i'm going to turn right around the number right click on this i'm going to go to properties right click properties. 17 00:02:30.990 --> 00:02:51.090 William Rosener: i'm going to call this one C and D sitting then under the caption in the same country on the type city number pizza reversal here on the right click recall this one CND profit and i'm going to get to profit within the parentheses. 18 00:02:54.030 --> 00:03:05.910 William Rosener: Okay, and we close that out because it before doing so let's go ahead before working on city let's take a look at the store number that's going to simply a sort all of this data by store. 19 00:03:06.870 --> 00:03:17.670 William Rosener: Okay So the first thing i'm gonna do is i'm going to be taking a look at the range eight to 32 and the exile down it's going to go all the way to the end of the document and here's what I was talking about that sort. 20 00:03:18.270 --> 00:03:27.180 William Rosener: So i've taken a look at 232 i'm going to start here so i'm going to be grabbing all this data hey to 32. 21 00:03:29.430 --> 00:03:46.350 William Rosener: And notice i've specified the header as know, and for that I if I wanted to I could have specified a one through D one and going all the way down, but by specifying by putting the header at know i'm going to start sorting right away. 22 00:03:47.640 --> 00:03:55.950 William Rosener: Okay, so you have the header on or not, but the big thing here is i'm going to be sorting this first one, the key is going to be on a two. 23 00:03:56.400 --> 00:04:05.400 William Rosener: So take a look, that this is what i'm using as my sort key when I get into a sorting by country and the buyers depressed that on. 24 00:04:06.150 --> 00:04:17.730 William Rosener: it's going to sort by country, but notice here like a United States at first sorts by country and that also turn around and sort it a second sort by city, and if we take a look at that one. 25 00:04:19.110 --> 00:04:29.730 William Rosener: So the first key was my country then i've added a key to which is going to sort by city okay well let's go ahead and Double Click on city. 26 00:04:30.990 --> 00:04:33.420 William Rosener: And I am just going to grab the code right here. 27 00:04:34.920 --> 00:04:44.340 William Rosener: A neuro store copy it and paste it in, week and leave everything alone, the only thing we need to do is rather than sort in it by eight to. 28 00:04:45.900 --> 00:04:49.740 William Rosener: i'm going to sort it by see to that's going to sort by the city. 29 00:04:51.000 --> 00:04:59.580 William Rosener: So simply change that to a scene and i'm going to leave it in ascending order and again i'm assume, since I started a day to I do not have a header. 30 00:05:00.690 --> 00:05:12.690 William Rosener: Okay, so if I now run that I now have the option to sort by city country and storks ever them Okay, then press the design mode now we're going to do profit. 31 00:05:14.430 --> 00:05:17.520 William Rosener: You and i'm just going to grab any one of these. 32 00:05:18.870 --> 00:05:32.400 William Rosener: i'm going to paste it here and i'm going to do, changes to the two, and if you wanted to go to a later on we'll go in a decent in order, and we do our find the highest and some is simply I will leave this one alone. 33 00:05:33.870 --> 00:05:38.160 William Rosener: Okay, so at this point, you should now be able to sort by country. 34 00:05:39.210 --> 00:05:54.300 William Rosener: By sitting by profit and by storm okay now I need to create two more command buttons to sort of define the to lowest and the two highest profits so i'm going to turn on my design mode select one of these copy it paste it. 35 00:05:56.700 --> 00:05:58.740 William Rosener: And maybe i'll paste it twice. 36 00:06:00.510 --> 00:06:04.290 William Rosener: i'm now going to right click on it right click go to properties. 37 00:06:06.240 --> 00:06:07.380 William Rosener: See md. 38 00:06:10.020 --> 00:06:13.440 William Rosener: lowest profit and number come on down here. 39 00:06:18.960 --> 00:06:26.670 William Rosener: In a little tune in parentheses and kind of repeat something very similar here, this one is going to be cmt. 40 00:06:27.900 --> 00:06:28.650 William Rosener: highest. 41 00:06:30.120 --> 00:06:32.610 William Rosener: offers and change the caption. 42 00:06:42.270 --> 00:06:51.060 William Rosener: Okay, so I gave it a different name as some when I Double Click on that i'll try and show you there and then I changed what appears on the screen, which is the caption. 43 00:06:51.570 --> 00:07:00.930 William Rosener: So we're going to first of all take a look at the lowest profit and you can see right here that was name I just so when I click on it it's going to execute this code. 44 00:07:02.310 --> 00:07:09.390 William Rosener: So what i'm gonna do is i'm going to come right here and i'm going to grab this code from profit. 45 00:07:11.610 --> 00:07:22.860 William Rosener: So I want to first of all make sure it is the data has been sorted by profit now what I can do is simply grab so what i'll tell you i'm about to run this. 46 00:07:26.370 --> 00:07:33.870 William Rosener: So I have sorted by profit, I now know these two pieces of data, right here, this is going to be see. 47 00:07:34.770 --> 00:07:54.690 William Rosener: wrote two column for and wrote three column for are my two last pieces data and what I want to do is, I want to take this piece of data and put it here this piece of data and put it here, and so this is going to be 1234567 so when we come back here to lowest profit. 48 00:07:55.830 --> 00:07:56.910 William Rosener: To my design mode here. 49 00:07:59.250 --> 00:08:03.270 William Rosener: i'm a simply take a look at cells to seven. 50 00:08:05.640 --> 00:08:14.790 William Rosener: is equal to so cells to you can see there bro form then we'll take ourselves. 51 00:08:16.950 --> 00:08:22.560 William Rosener: three seven is equal to cells. 52 00:08:23.910 --> 00:08:25.110 William Rosener: Three four. 53 00:08:26.700 --> 00:08:36.660 William Rosener: Okay, so i'm gonna take the information and cells to four and put it over there in two, seven and three four goes over there, so now, when I run this. 54 00:08:38.820 --> 00:08:47.100 William Rosener: it's going to extract those values Okay, one might be nice is let's assume we had sort of a store and I want to leave it sort of ice storm. 55 00:08:48.150 --> 00:08:55.680 William Rosener: Somebody come right back here Double Click on lowest profit and i'm going to grab this code right here and paste. 56 00:08:58.980 --> 00:09:14.280 William Rosener: It would you cannot bring those back so what's going to happen when I press on lowest profit, first of all sorts of by profit I extract the values, then I turn around and sorted by store number, and it will be so quick. 57 00:09:15.360 --> 00:09:19.200 William Rosener: That the user won't even see that it has happened, and so Now let me go ahead and press. 58 00:09:20.220 --> 00:09:28.290 William Rosener: On the design moon hit store actually will delete these values here so it's sorted by store, I am now going to prime the lowest profit. 59 00:09:29.160 --> 00:09:43.170 William Rosener: And you can see what did what we did is we sorted by profit extracting the values, then we turn right around and restarted it by store number and let's go ahead and do something very similar to get the highest. 60 00:09:44.370 --> 00:09:48.990 William Rosener: So in this case i'm going to press on the design moon Double Click on highest. 61 00:09:50.640 --> 00:09:54.810 William Rosener: And, just like I did here i'm the first of all sort this data. 62 00:09:58.170 --> 00:10:10.230 William Rosener: I probably should be challenging you here on oh it's nothing too excited here but let's go ahead and one big thing here is really in sort of this one ascending order we're going to put this in descending. 63 00:10:11.580 --> 00:10:29.970 William Rosener: So I changed that from the sending to descended so let's go ahead and right now run this with the highest profit and you can see here is the highest province and auto where there's take this piece of data and place it here, and this piece of data and place it there. 64 00:10:31.200 --> 00:10:37.170 William Rosener: Okay let's come back here the highest profit i'm now going to go ahead and take what's themselves. 65 00:10:38.250 --> 00:10:44.970 William Rosener: To for the amount of store that someone sells for work comma sudden. 66 00:10:46.440 --> 00:11:02.430 William Rosener: that's equal to what was in cells to come aboard just like looking down here below so i'm gonna put that in for my form so right here, this is going to be row for calm seven. 67 00:11:04.530 --> 00:11:05.760 William Rosener: And then i'm going to take what's in. 68 00:11:06.990 --> 00:11:10.890 William Rosener: cells, three, four, in place that information implied seven. 69 00:11:16.440 --> 00:11:23.670 William Rosener: Okay hey let's go ahead and check this out with some design mode and it the highest profit. 70 00:11:25.980 --> 00:11:27.390 William Rosener: And sure enough it's grabbing. 71 00:11:28.410 --> 00:11:37.230 William Rosener: This again let's go ahead and put it back so let's assume we want to grab it, but we still went on leave it pretend that there are me and leave it sorted by store number. 72 00:11:38.880 --> 00:11:49.410 William Rosener: So i'm going to do is Double Click here and just like right here, I could grab it here or I could come down here to the store number either one of these places grab this code. 73 00:11:51.750 --> 00:11:52.350 William Rosener: and 74 00:11:53.370 --> 00:11:55.950 William Rosener: here's the highest profit and paste it. 75 00:11:59.610 --> 00:12:15.480 William Rosener: Okay, so what did we do have the highest profit, just like we did the most profit, we first sort the data this case I did a decent in order I grabbed information put them in the appropriate cells, then i'ma turn right around and resort it back so the store number. 76 00:12:16.650 --> 00:12:17.760 William Rosener: Okay, so let's go ahead and. 77 00:12:19.260 --> 00:12:25.020 William Rosener: delete this information on and turn off the design mode so here's my lowest profit. 78 00:12:26.160 --> 00:12:44.790 William Rosener: It was the highest profit and there we go some anyway, so now once the information has been something we're sorted by store we hit highest profit it's so quick its first sorts it by profit extracts the values, then turns right around and resorts it by store number. 79 00:12:46.320 --> 00:12:54.690 William Rosener: So hopefully this video you'll learn a little bit about how to sort you seen that bba on Microsoft excel and i'll catch you next time, thanks man.