WEBVTT

00:02.290 --> 00:03.140
Okay guys.

00:03.160 --> 00:05.780
Welcome to this chat now just for fun.

00:05.800 --> 00:11.990
I'm going to show you how I generated all the car registrations that we used way back in Section 2.

00:12.250 --> 00:16.870
Okay now what we're going to do is combine all the elements that we've already covered in this course

00:17.170 --> 00:21.920
and we're also going to introduce a new one which is round between.

00:21.970 --> 00:27.020
So the first thing we're going to do is build a look up table of all the letters of the alphabet.

00:27.250 --> 00:33.370
So down here we're just going to add the numbers 1 through 26.

00:33.370 --> 00:34.620
Where are we.

00:34.900 --> 00:43.060
1 through 26 and next to that when you add the letters in caps A B C all the way down come back to you

00:43.060 --> 00:45.970
in just a second when and fill all those in.

00:46.930 --> 00:53.290
Okay so now we have all of our letters of the alphabet down here so one can do is just highlight over

00:53.290 --> 00:53.620
that.

00:53.650 --> 00:56.520
And I'm going to add this as a named range.

00:56.530 --> 01:00.000
So I'm just going to call this letters.

01:00.550 --> 01:01.280
Hit enter.

01:01.330 --> 01:03.160
So there's our first data array.

01:03.190 --> 01:04.700
I think have the CAPS LOCK ON.

01:04.830 --> 01:07.410
Alex a little bit more attractive that they get Okay.

01:07.420 --> 01:12.350
The next thing we're going to do is hands on the table for the first numeric in there.

01:12.370 --> 01:15.000
Now I can't actually remember how these look in the real world.

01:15.010 --> 01:19.810
So I'm just going to put one two three here and I think we have zero.

01:19.810 --> 01:20.920
I think we have five.

01:20.920 --> 01:22.970
And I think we have six.

01:23.140 --> 01:27.520
And then I'm just going to change that to a data table by clicking in here.

01:27.670 --> 01:32.560
See if I've got the caps lock on not now and call that number.

01:33.190 --> 01:39.300
Okay so now we have our two data tables we have our letters and their numbers for the first digit.

01:39.310 --> 01:47.170
Now what we're going to do over these cells is just create a random entry from this column or from this

01:47.170 --> 01:47.580
column.

01:47.590 --> 01:49.270
So let's start building here.

01:49.270 --> 01:59.950
So here we're going to say equals round between open brackets and our bottom number is going to be number

01:59.950 --> 02:02.530
one because we're going to be looking at the alphabet.

02:02.560 --> 02:07.480
Our top number is going to be 26 close brackets and here Centre.

02:07.480 --> 02:15.220
So now you can see that we have 18 entered in here now all going to do is take that 18 and use that

02:15.310 --> 02:18.750
as a look at value from this data table over here.

02:18.940 --> 02:27.390
So just before round between now well inside look up this is going to be our look at Ballie the 18 just

02:27.630 --> 02:28.660
come out.

02:29.200 --> 02:37.270
And while I want to look at the letters table comma and one read from column 2 which is where the letter

02:37.270 --> 02:39.650
leaves come out right.

02:39.800 --> 02:40.610
FALSE.

02:40.660 --> 02:41.810
And hit enter.

02:42.130 --> 02:42.670
So they go.

02:42.670 --> 02:46.940
We now have our first letter which is going to be f.

02:47.050 --> 02:54.190
So all they do is dragging down that part let's say eventually we're going to get around 200 lines.

02:54.250 --> 03:00.640
Now all we need to do is just base that in fact let's go down 200 lines down down and then now all the

03:00.640 --> 03:05.100
way to 200 because we have 200 employees control and deep.

03:05.380 --> 03:05.920
So they go.

03:05.920 --> 03:14.380
We now have 200 randomly generated letters as the first letter of our number plate in the UK we have

03:14.380 --> 03:18.120
two letters at the start so I'm just going to copy that across there.

03:18.130 --> 03:20.280
So there's two letters.

03:20.290 --> 03:23.630
The next thing we have is one of these numbers here.

03:23.680 --> 03:26.770
I think if that's not correct you can just put the right numbers in.

03:27.070 --> 03:35.980
So what we're going do very similar We're going to say equals round between open brackets and this we're

03:35.990 --> 03:39.430
going to have the lowest number as one and the highest number is three.

03:39.430 --> 03:44.440
So it's going to be around between 1 come a 3 and enter.

03:44.530 --> 03:45.560
So they go.

03:45.590 --> 03:49.140
So no all we need to do is exactly the same you look up over here.

03:49.150 --> 03:58.750
So look at open brackets skip over this because this is going to be our look up value comma numbers

03:59.620 --> 04:07.080
comma too because we want to pull from the second column comma false and close brackets.

04:07.270 --> 04:10.880
And I'm just going to copy that all the way down by to click in the right hand up.

04:10.980 --> 04:18.300
So they go we have a random assortment of the zeros six or five is in line with this table here.

04:18.570 --> 04:24.070
Ok then in the next column columns a little bit more simple because this is just literally a random

04:24.070 --> 04:26.540
number between 0 and 9.

04:26.710 --> 04:37.120
So what we do say equals round between open brackets 0 and 9 close brackets.

04:37.270 --> 04:41.310
So they go so you can see the number plate is starting to take effect.

04:41.310 --> 04:48.190
So we have three letters loops two letters two numbers and then the next thing we have are the three

04:48.250 --> 04:54.880
letters that appear at the end so I'm going to copy that control see pace and then fill it across using

04:54.880 --> 05:00.580
control and our control them are highlight all of those three and then just double click that.

05:00.580 --> 05:01.600
And that fills it down.

05:01.820 --> 05:11.620
Now across these columns we've successfully randomly built a full UK car registration plate a case and

05:11.630 --> 05:12.000
makes it.

05:12.010 --> 05:15.970
We're going to do is join them together and we've done this before with the cat and eights.

05:16.160 --> 05:18.820
So I say it equals concat.

05:18.990 --> 05:27.050
Where am I in apes opium racket so I'm just going to point to these this one comma this one Cummer this

05:27.050 --> 05:29.650
one comma this one comma.

05:29.840 --> 05:37.240
Then we normally have a space in the space enclosed in quotes.

05:37.520 --> 05:44.870
And now our last three letters this one comma this one comma this one and close brackets.

05:45.260 --> 05:45.890
So they go.

05:45.890 --> 05:49.600
There's our first randomly created registration plate.

05:49.610 --> 05:52.850
They were wrong and you just copied them to underlinings bubble

05:56.070 --> 05:56.690
control.

05:56.720 --> 06:04.090
And now to make sure that this is a unique value that is this registration of olympia's once we can

06:04.110 --> 06:09.080
dump this formula to make it horrendously complicated or what I normally do with these kind of things

06:09.140 --> 06:10.740
is I just cheat.

06:10.850 --> 06:18.810
So I use the formula equals count if what going to say is have a look at this range here.

06:18.830 --> 06:20.780
This is our range in the county.

06:21.200 --> 06:24.470
Here's EF 4 because we always want to use that range.

06:24.490 --> 06:29.270
And when we drank the formula down karma and then look at that value.

06:29.270 --> 06:37.220
So we're going to do is find the Kennards of that value in that range and close brackets and hit enter

06:37.400 --> 06:42.750
and you can see as each time I hit enter that the numbers change and they recalculate.

06:42.770 --> 06:44.610
So double click.

06:44.850 --> 06:52.460
That's what we hope to see is a 1 against absolutely every cell in here rather than scroll them all

06:52.460 --> 06:55.650
the way to the bottom because we've already got this highlighted.

06:55.820 --> 07:04.610
We can't you see down here that the sum is 200 which is exactly what we want 200 unique individual U.K.

07:04.610 --> 07:06.070
licence plate numbers.

07:06.230 --> 07:06.760
Okay.

07:06.890 --> 07:10.480
And that's how I generated those for use in our database section.

07:10.580 --> 07:11.040
Okay.

07:11.090 --> 07:12.350
So you in the next lecture.
