Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
Okay, This is the final lesson of this module. And in this listen, we're going to be talking about how to manipulate and fix data you have important. So we have learned how to import data,
00:13
actually have seen how to import data from different kind off sources, different kind of programs, different types of data. Also, you have seen the difference between
00:26
opening and importing data.
00:29
And now let's go to some examples on how to fix this data. So
00:37
presumes that there is something wrong with that data. So way have imported this
00:46
couple off
00:49
lines from three different files.
00:52
And we have by mistaking port IDs
00:57
ah,
00:59
line with which is basically a header off the file and then some data, of course, that there could have been much more. So it's just the example. So we did it from what say three people
01:10
and we want to put it together. And now we we have this problem that we have multiple rows had a rose, which we don't need. Now if we have just three files that it's no big deal that can imagine you have 50 of them or something like that.
01:29
So,
01:30
in order to do that. Ah, you just go to data.
01:34
Uh, tab, you go to date the tools
01:38
form, uh, group, and you click on remove duplicates
01:45
and then you'll get this dialog box in which you excel. We will ask you. Which columns do you want to look in for? Duplicates.
01:56
And of course, you have this check box which has made it has Heather's.
02:00
And ah, in this case, you wantto uncheck it
02:04
because if it's checked, then it won't look at it. So So the great portion of the worksheet is what excel we'll look at when looking for duplicates.
02:16
So, um,
02:20
we are jacket and then we can say Okay. And we got this information that to duplicate values were found and removed. Four unique. Well, he's remained. So this is a unique rebel. You and all of these mileage is
02:32
our unique. Why? Because in order for excel to remove it, everything has to be identical. So every single cell in a row in a selection has to be identical in between two rows. In order for one of them to be removed, The one removed is always the one that is below the 1st 1
02:52
that is part of the group off identical rose. So we had three.
02:55
He had a rose, two were removed. The 1st 1 was kept just to give you an idea what would happen if we would
03:07
not uncheck? My data has headers
03:12
s O. If it's really we checked,
03:15
um,
03:16
we'll have one duplicate really removed because Excel will then treat
03:23
the mileage as a heather
03:27
and, uh, sorry. The first line is a header. And then look what we have duplicated and we have two more headers. And the 1st 1 will remain because in that case, excel things. Okay, this is the 1st 1 The 2nd 1 is duplicate. I'm going to remove it.
03:46
So there's the first thing. The second thing is, can you imagine that you have imported the data from, let's say, a word or text file
03:57
and it was delimited by spaces instead of commas or tabs. And you made a mistake when importing and you got
04:04
all of the data in one column
04:06
and the values were separated by
04:11
spaces.
04:12
You select the problematic data
04:15
and you click in text two cones
04:17
and it as crazy, the limited or fixed with because it can do it with fixed with this well, but you say delimited,
04:25
you get the preview of selective data
04:29
and then you choose what is the deal emitter? So in this case, it is space.
04:33
It is pretty much the same, like would see us, we import.
04:38
But now it's going to fix it. And you see, the preview is going toe Move these numbers in three columns like there should be
04:47
and we just pricks next.
04:50
And it the same thing, like when importing it asks us what is going to be the former
04:59
off the
05:00
the new generated celeb do populated cells
05:05
you want to General because in this case, these air numbers and you,
05:11
if it's generally will convert in America, is the numbers. So you say finish
05:16
and you get it. Those numbers. You see that because it's automatically aligned to the right.
05:24
And, um,
05:25
the third thing doesn't actually have to do nothing, something specifically with important data.
05:32
But if you have,
05:34
you've had this, for example, some list off part numbers for spare parts,
05:41
and now you have, ah, new nomenclature which says that we are losing the first number and we're no longer having bashes. We're just going to have dot between them
05:53
second and third number
05:56
and Ah,
05:58
So this is the road data.
06:00
In the 1st 2 lines, you enter how it should look
06:04
and you just become flesh feel
06:08
and excel presumes what you wanted to do
06:12
And, uh, it feels it automatically.
06:15
For example, we have some text and some number
06:19
and then some texts and some member
06:24
And then again,
06:26
some fixed and some number
06:29
and again
06:33
next, separated by space and a number.
06:39
And then you say, Just here, 20 in here 2340
06:44
and the You click here you do the flesh feel
06:50
and he doesn't see the better. So this is the explanation. Why So let's change this
06:58
and remove from here one letter. So let's say we have five letters and numbers and then we do flesh feel
07:08
it still doesn't do it. So these numbers have to be pretty
07:13
uniform.
07:15
For example,
07:15
there has to be two digits.
07:19
And if we change this to 23
07:25
and flesh feel, then excel, we say OK, I have five letters than space,
07:31
then two digits and I want to keep just two digits, and it will do it.
07:36
So, uh, this is how flesh will works.
07:41
So
07:42
these are three methods to fix the problems you have with your important data,
07:49
and that's pretty much all about importing and fixing them. And let's just do one thing more
07:58
and this is how to export data.
08:00
So if you could conceive as
08:03
you big folder
08:05
and you get this and then you have Excel Workbook
08:09
And here you have all the possible export for months, which are pretty much the same
08:16
as the important ones.
08:20
Mmm. So so that's it.
08:24
And you can just pick one you want. For example, you can
08:30
you can choose C s fi comma delimited
08:35
and its module five
08:37
and you can click here
08:43
and it tells you the same thing that
08:46
doesn't support support work books that contain multiple sheets because he s he doesn't support that.
08:52
So you want to say active sheet only? You say OK off course.
08:56
And some features in your world we might be lost. Of course, because CSP just a text file. They want to keep using that format.
09:05
And, uh,
09:09
you get it. It's now module five C s. V.
09:15
And when you look a TTE
09:18
uh,
09:20
file we were talking about.
09:31
So if we look at this file,
09:35
be open it in excel.
09:37
Yeah, that's the same file.
09:41
But we can also open that bid
09:48
example Note bed
09:50
and you would get the C S V file.
09:54
Just comma separated values. So we have
10:00
these air combs, these air rose, and this is how CS refile looks.
10:05
So this is basically all about exporting data. So,
10:09
uh, that's it.
10:11
And with this, we're concluding this model.
10:16
Sorry. There's going to be a recap after this.

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor