00:00The Excel filter tool is a superb method for sorting your data set. However, what
00:05if I tell you you can automatically and dynamically sort your data set?
00:09Does that pick your interest? Well this is how I do it. On your worksheet you're
00:13gonna right click on it and from the context menu you're gonna go to view
00:17code. In here you're gonna paste this VBA script. If you like a copy of this VBA
00:21script, please leave a request on the comment section of this video. There's a
00:25couple of things that you can customize here so that it meets your data set
00:29requirement. The first line indicates column C which is your sorting column.
00:33This is the column that I'm gonna sort based on. If your column you're
00:38sorting based on B, you're gonna put B colon B here like this. And then the
00:42second line on the VBA script here is a range for your data set. Mine goes from
00:47column A to column C. That's why I'm saying A colon C like this. Now this
00:53particular subroutine get triggers whenever you make changes to your
00:57worksheet here. So when you go in there, what it does is that if it's within that
01:02range of a sorting, which means if it's within that column C that you're making
01:07changes to, then you start applying the sort function here. So before that I
01:13determine what column I'm targeting which is gonna be column C for my case here.
01:19And there's a couple of key items that you can change here. One of them is the XL
01:24descending. Right now if I make any changes it will do descending order which
01:28I'll show you in a bit. You can change this to ascending if you like. Well let's
01:32do a test here. Let's control S to save and let's minimize this BBA editor here.
01:39Now if I were to go ahead and make some minor changes to my data set, let's say for
01:43example I want to change this to instead of 50,348 I'm gonna say
01:4910,000 you see what happens here. You can see that it is in descending order and
01:54Ted gets on the third on the line here. Let's say for example if I put this to
02:00say 30 you can see that the lead jumps down way below on the list here. Likewise
02:07this also applies when you add a new record. Let's say I'm gonna add a new
02:11salesperson called James. Oops James there you go. And then he's from Canada and
02:17let's give him some total sales figure like this. You can see James goes up the
02:22food chain here and he's on row number 10 here. Let's go ahead and look at our
02:27order now. Let's say for example if you want to change this to ascending simply
02:32change this to ascending like this. Control S to save and let's minimize this guy
02:37here. Now if you make any changes here let's say if I change it to 15 you can
02:41see that the smallest number goes way up top on your data set here. It's that
02:46simple how you you can make those changes. Now another thing I want to show you
02:50right-click view code here is to adjust your sorting range here. Now I'm sorting
02:56based on column C and for some reason you want to sort based on column B all you
03:01have to do is put B colon B like this. Control S to save let's minimize this guy
03:07here. So now if I were to change make any changes because changes triggers that
03:12subroutine there. Let's say if Ted has moved from Japan to say Canada you can
03:18see that everything get sorted according to ascending order and likewise if you
03:24want to seed it in descending order you say descending here. Control S to save and
03:31minimize it and now if I were to change Haley to US like this you can see that
03:37everything gets sorted in this ascending order.
Comments