WEBVTT

00:01.190 --> 00:03.360
Okay guys and welcome to this lecture.

00:03.530 --> 00:10.900
Now whenever I meet someone that uses Excel one of my first questions is have you learned how to program.

00:11.270 --> 00:17.990
Now most people the immediate response would be why would I need to learn how to program or the second

00:17.990 --> 00:20.330
response would be isn't it difficult.

00:20.390 --> 00:22.360
Okay let's address them one at a time.

00:22.370 --> 00:24.540
Why would you learn how to program.

00:24.540 --> 00:30.920
Now if you have a thousand jobs that you need to do with excel in every single one of those thousand

00:30.920 --> 00:36.550
jobs is unique has no common characteristics in your never have to do them again.

00:36.560 --> 00:43.140
There is absolutely no point in learning how to program because coding cannot help you in those situations.

00:43.340 --> 00:48.950
But if you have a thousand jobs in Excel and some of them you do every day or some of them every week

00:48.950 --> 00:52.850
or some of them every month reporting is a is an excellent example.

00:52.880 --> 00:57.760
Then you absolutely must learn how to program for three reasons.

00:57.800 --> 00:59.870
Speed and speed.

00:59.900 --> 01:06.290
The difference you can get by automated a process using programming is staggering which is the point

01:06.290 --> 01:12.380
of this lecture which I'm going to demonstrate as we go through it at accuracy no matter how hard we

01:12.380 --> 01:13.060
try.

01:13.130 --> 01:21.080
Humans will always make mistakes or if they don't make mistakes they are working at such a slow pace

01:21.080 --> 01:25.940
to ensure accuracy then it would just gobble up your entire day.

01:26.090 --> 01:30.230
And the third one is reliability or repeatability.

01:30.230 --> 01:37.820
If the tasks that you have are repetitive all you have to repeat those tasks it is begging to be automated

01:37.850 --> 01:41.160
with a little bit of programming a little bit of code in behind it.

01:41.270 --> 01:45.180
You can make things fast accurate and repeatable.

01:46.110 --> 01:51.330
Now the other concern that I mentioned was people think that it's difficult to program now people of

01:51.330 --> 01:55.080
a certain age may remember a show called Buffy the Vampire Slayer.

01:55.380 --> 01:58.840
No in Buffy the Vampire Slayer in this one particular episode.

01:59.070 --> 02:01.990
Zander was having trouble getting the girlfriend now.

02:02.060 --> 02:08.060
RS who is one of the gang played guitar in a band and Zander realize that chicks dig.

02:08.060 --> 02:11.700
Musicians as Zander said so Zander says it was.

02:11.760 --> 02:17.520
I need to learn to play an instrument and he says you play guitar he says is it difficult to play guitar.

02:17.760 --> 02:24.870
And also says not the way I play it which is a very poignant statement because I'm sure programming

02:24.870 --> 02:29.300
can be horrendously complicated but not the way I'd do it.

02:29.370 --> 02:34.950
So you're going to learn to program the way I learn to program and you'll find that although it may

02:34.950 --> 02:40.890
look complicated the first time you see it you will grasp this so easily you'll wish you had done it

02:40.890 --> 02:41.830
years ago.

02:42.150 --> 02:43.430
Okay let's crack on.

02:43.690 --> 02:48.800
Now here's a file that you'll be able to download and begin to look at coding this a bit later.

02:48.810 --> 02:55.050
But for the moment this is just to demonstrate the speed difference and the accuracy differences between

02:55.050 --> 02:59.250
doing something manually and doing something in code.

02:59.250 --> 03:05.160
Now what we have is an employee list in a particular format and what we have is employ no name National

03:05.160 --> 03:11.040
Insurance number and a member neller land until we get back down to employee number so we have employee

03:11.040 --> 03:18.570
number down to car reg and there's our first record we have menc triples in a row one and then as we

03:18.570 --> 03:30.390
move down we have MNC triple 0 2 down to here and MNC triple 0 3 down to here and we have a total Ah.

03:32.580 --> 03:34.640
200 records now.

03:34.710 --> 03:39.260
Imagine that you get this file from your head office or whoever and unique to it.

03:39.420 --> 03:42.220
And you need to convert it into a table.

03:42.300 --> 03:44.340
So you can do v look ups from it.

03:44.670 --> 03:49.230
Now I've already program this just to give you a demonstration of the speed difference.

03:49.230 --> 03:56.070
But let's imagine for a second that we're going to be doing this manually so I'm moderately okay at

03:56.070 --> 03:59.230
Excel and I'm reasonably know what I'm doing.

03:59.370 --> 04:04.410
So I'm just going to start the time run my phone and see how long this takes to do manually.

04:04.410 --> 04:06.170
So let's go.

04:06.510 --> 04:06.800
Okay.

04:06.800 --> 04:08.610
First they need to copy the titles.

04:08.610 --> 04:10.880
So I'm going to copy that long come over to d one.

04:10.910 --> 04:12.620
I am going to hit transpose.

04:12.690 --> 04:13.530
Boom.

04:13.790 --> 04:16.500
Okay there's my first recording and a copy.

04:16.500 --> 04:21.110
In fact when only use keyboard shortcuts from now on allowing them to transpose that then come down

04:21.120 --> 04:27.820
here record two down to record three control and see back up to here right click transfers down here

04:27.830 --> 04:35.600
record 3 dance record for control and say Back up here and paste mistake control and said I meant to

04:35.610 --> 04:43.620
transpose right click transpose K down to and away I just clicked on down to record 4 days record 5

04:44.010 --> 04:48.460
karpeh Scroll back right click transpose and stop.

04:49.240 --> 04:55.780
Okay forty two point seven eight seconds on Makoto I have two hundred records so don't worry.

04:56.000 --> 04:56.970
But here we go.

04:57.160 --> 05:03.480
And we can see that we now have our titles across the top and we have each of our records in sequence

05:03.480 --> 05:11.700
so we have record one record two three four now at the moment so let's assume we already add the titles

05:11.700 --> 05:18.870
in and we have four records in let's say took two point seventy two point eight seven seconds to do

05:18.870 --> 05:25.270
the titles and then it took 10 seconds a record nay because some work at the top of the day so obviously

05:25.350 --> 05:28.400
quick to get from here back up to here.

05:28.560 --> 05:35.810
But imagine by the time we go to I'm only down to line 500.

05:36.090 --> 05:39.010
Let's go back.

05:39.660 --> 05:41.430
Let's get to the thousands at least again.

05:41.610 --> 05:45.960
So now I'm down to line one thousand and record.

05:45.970 --> 05:47.520
I can't see where the recall is.

05:47.520 --> 05:48.470
There we go.

05:48.990 --> 05:49.350
Goodness.

05:49.350 --> 05:50.760
We're only up to em.

05:50.760 --> 05:56.570
NC 76 and we go down to 200 wasn't it.

05:56.760 --> 05:58.210
So we go down to 200.

05:58.260 --> 06:02.230
So then we've got to keep scrolling down and copy and then coming back up to the top here.

06:02.370 --> 06:04.200
Right click transposing.

06:04.320 --> 06:09.800
I think it will probably take longer than 10 seconds per record as we get further down the list.

06:09.870 --> 06:13.600
But for argument's sake let's call it 10 seconds per record.

06:13.920 --> 06:20.530
So we have two inch records do so let's say because I'm useless at mass unless I use Excel.

06:20.550 --> 06:26.650
Let's say we took 10 seconds a record and we have 200 records to do loops.

06:26.670 --> 06:34.490
Cause there are times two hundred boom 33 minutes and 20 seconds.

06:34.530 --> 06:36.310
So just so I can remember that.

06:36.360 --> 06:39.950
I'm just going to put that in here.

06:39.970 --> 06:48.430
Let me just copy and paste special values and it format the same way down an ISO format panes.

06:48.760 --> 06:49.620
And pump.

06:49.710 --> 06:53.320
Say again if we were to do that manually.

06:53.640 --> 07:01.500
And you'll realize that we made a mistake so there is every chance he could make errors while you transpose

07:01.500 --> 07:03.280
in two sets of data.

07:03.600 --> 07:09.630
But at 10 seconds per record it's going to take you 33 minutes and 20 seconds.

07:09.830 --> 07:12.000
It still eat this lot.

07:12.060 --> 07:16.610
Now imagine that we had a piece of code that would do a similar thing.

07:16.770 --> 07:18.050
Now I don't need to know that.

07:18.120 --> 07:23.650
No I don't need to use my stopwatch on this particular one because I already have programmed in these

07:23.670 --> 07:27.450
start and stop time and then we can quickly work out what the difference is.

07:27.440 --> 07:32.600
So how long the tow code takes to run the savel look at the scope of this.

07:32.640 --> 07:37.100
So we have two thousand eight hundred lines of data.

07:37.260 --> 07:47.480
We have 14 individual entries and we have two hundred records each with 14 entries and it takes us 33

07:47.520 --> 07:51.430
minutes and 20 seconds generously to do it manually.

07:51.450 --> 07:55.130
So let's have a look how long that's going to take if we did it in code.

07:55.320 --> 07:59.850
Now we don't need to use a stopwatch because I've already programmed in the start and stop times for

07:59.850 --> 08:03.840
this and we can just do the calculation at the end to see how long it takes.

08:03.840 --> 08:05.410
So let's go.

08:05.770 --> 08:07.230
Okay we're done.

08:07.230 --> 08:15.300
So now we have the table bell so our employee name which is the employee name sorry the employee number

08:15.390 --> 08:16.250
two number.

08:16.260 --> 08:23.610
We have name two name nascence shows a massive insurance number and then land land and then add all

08:23.610 --> 08:30.600
the different things over here and we can see that we have this record at MNC one hundred konneh Phillips

08:30.600 --> 08:31.200
come fill it.

08:31.300 --> 08:34.840
JT five nine black lab out and all the way down.

08:34.840 --> 08:37.180
Let's just go down to the bottom to do a quick sound check.

08:37.180 --> 08:42.790
So number two hundred is Eileen Collins and back up their date they go.

08:43.180 --> 08:46.700
Employee number 200 is indeed aisle Collins.

08:46.720 --> 08:53.570
So manually that would have taken us 33 minutes and 20 seconds.

08:53.740 --> 09:00.840
And here are my start and stop times I'm just going to format these three cells to show urse.

09:00.860 --> 09:03.700
Chris we are that interested.

09:03.700 --> 09:13.810
We're going to go two hours minutes seconds and nearly seconds for some reason my screen is having trouble

09:13.810 --> 09:16.090
update in Sago itself.

09:16.110 --> 09:21.240
The code at 11 0 4 and twenty nine point four to nine seconds.

09:21.430 --> 09:23.320
And it finished a little while later.

09:23.320 --> 09:24.400
So let's have a look.

09:24.410 --> 09:26.540
Equals that minus.

09:26.740 --> 09:29.940
That's one second.

09:29.960 --> 09:32.680
And Point one milli second.

09:32.800 --> 09:35.770
So let's be generous and knock off the point one millisecond.

09:35.770 --> 09:44.070
So that took one second to run compared to 33 minutes and 20 seconds.

09:44.200 --> 09:45.790
If we were to do that manually.

09:45.940 --> 09:49.580
Okay so what's the difference so we've got 33 minutes.

09:49.850 --> 09:53.750
And there were six sessions in it.

09:53.890 --> 10:01.180
So we have one thousand nine hundred eighty seconds in Thirty three minutes plus 20 seconds.

10:01.270 --> 10:10.840
So we're talking two thousand seconds to do that job manually vs. one second to do it automatically

10:10.870 --> 10:12.050
using code.

10:12.070 --> 10:14.440
So they go how fast is it.

10:14.440 --> 10:19.320
It's two thousand times faster and 100 percent accurate.

10:19.330 --> 10:23.750
If you are using code and that is the reason that you should learn to code.

10:23.980 --> 10:24.490
Okay.

10:24.550 --> 10:26.360
I'll see you in the next lecture.
