1
00:00:00,00 --> 00:00:05,800
Welcome to excel champs in todays video we 
are going to learn about six powerful

2
00:00:05,900 --> 00:00:10,700
methods to remove the duplicates from our 
data first of all we will use pivot tables

3
00:00:10,800 --> 00:00:15,600
to identify the duplicates in our data set 
now here I have a small example

4
00:00:15,700 --> 00:00:20,500
I have name of few students with the subject 
and the year of passing out

5
00:00:20,600 --> 00:00:25,400
first of all I'll create a pivot in the same 
excel sheet I'll go to insert

6
00:00:25,500 --> 00:00:30,300
00:30.3 insert a pivot table select existing 
worksheet and select a range here

7
00:00:30,400 --> 00:00:35,200
00:35.2 and select the cell F22 and press 
ok we will put

8
00:00:35,300 --> 00:00:40,100
the name in the rows than subject and than 
year

9
00:00:40,200 --> 00:00:45,000
now I have put everything in the rows now 
this looks quite confusing

10
00:00:45,100 --> 00:00:49,900
we'll go to desgin and change the report 
layout we'll go to show in the tabular form

11
00:00:50,000 --> 00:00:54,800
now here I'll again go to design go to subtotals 
and

12
00:00:54,900 --> 00:00:59,700
do not show subtotals now here I got the 
name of the students

13
00:00:59,800 --> 00:01:04,600
the subjects and the year of passing out 
so these are my unique entries now lets

14
00:01:04,700 --> 00:01:09,500
try this out I have a lsit of 13 students

15
00:01:09,600 --> 00:01:14,400
and here I got the list of 10 students it 
means this data set contains

16
00:01:14,500 --> 00:01:19,300
three duplicate values so this is how you 
can refine your data and get the

17
00:01:19,400 --> 00:01:24,200
unique values using a pivot table option 
now second method is using

18
00:01:24,300 --> 00:01:29,100
power query now first of all it is always 
advised to covert your

19
00:01:29,200 --> 00:01:34,000
data into a data table how I'll do this I'll 
simply click anywhere

20
00:01:34,100 --> 00:01:38,900
on my data and press CNRT+T from my keyboard 
and press enter

21
00:01:39,000 --> 00:01:43,800
now this is changed into a table what I'll 
do is I'll click on the first cell go to 
the

22
00:01:43,900 --> 00:01:48,700
data and click on from table range here my 
power query editor has

23
00:01:48,800 --> 00:01:53,600
opened now there are two methods of removing 
the duplicates

24
00:01:53,700 --> 00:01:58,500
first of all you can click on this table 
you will get a few options

25
00:01:58,600 --> 00:02:03,400
and you can simply click on remove duplicates 
now here I got

26
00:02:03,500 --> 00:02:08,300
my ten unique entries and my duplicate values 
which were three in count

27
00:02:08,400 --> 00:02:13,200
have been removed now I'll again I'll remove 
this step again I got my

28
00:02:13,300 --> 00:02:18,100
13 values now the 2nd way is you can select 
your data complete data set

29
00:02:18,200 --> 00:02:23,000
go to home click on remove rows and remove 
duplicates

30
00:02:23,100 --> 00:02:27,900
it has again removed my duplicates from my 
data set and I

31
00:02:28,000 --> 00:02:32,800
got my unique values now I'll close close 
and load to table on

32
00:02:32,900 --> 00:02:37,700
existing and I'll copy this here so I got 
my unique values

33
00:02:37,800 --> 00:02:42,600
in the excel sheet now the third method is 
using the inbuilt function countif

34
00:02:42,700 --> 00:02:47,500
or countifs now here we have only one single 
criteria so we will use countif

35
00:02:47,600 --> 00:02:52,400
now to use countif first of all we will have 
to use a function called

36
00:02:52,500 --> 00:02:57,300
textjoin as the name suggest this function 
will combine the given ranges

37
00:02:57,400 --> 00:03:02,200
into one single cell first of all I'll use 
textjoin I'll define a delimiter

38
00:03:02,300 --> 00:03:07,100
since I do not need a space so I'll leave 
this blank I'll put

39
00:03:07,200 --> 00:03:12,000
true since I want to ignore my empty cells 
and than now I'll define

40
00:03:12,100 --> 00:03:16,900
my range press enter now you see the magic 
range of converting this

41
00:03:17,000 --> 00:03:21,800
data into a range I did not copy this formula 
till the end it was

42
00:03:21,900 --> 00:03:26,700
automatically copied till the end now I'll 
use countif function in this range first 
of all

43
00:03:26,800 --> 00:03:31,600
the array is range now my range is this column1 
and what is my criteria

44
00:03:31,700 --> 00:03:36,500
I just want to check if my this value is 
repeated in this given range

45
00:03:36,600 --> 00:03:41,400
so I'll press enter again I got 1 this 1 
means that

46
00:03:41,500 --> 00:03:46,300
this entry is occuring once and when I select 
2 this means that

47
00:03:46,400 --> 00:03:51,200
this text is occuring twice now lets select 
the 2 these are my

48
00:03:51,300 --> 00:03:56,100
repetative values now the next method is 
using conditional formatting now here

49
00:03:56,200 --> 00:04:01,000
lets try this conditional formatting I am 
selecting my data go to home and conditional 
formatting I'll

50
00:04:01,100 --> 00:04:05,900
select cell rules which are duplicates values 
now you will see it is going to

51
00:04:06,000 --> 00:04:10,800
highlight my values which are repeted it 
has highlighted physics because it is

52
00:04:10,900 --> 00:04:15,700
occuring twice or more than two times in 
my this column but this again does not make

53
00:04:15,800 --> 00:04:20,600
sense beacuse I want the combination of these 
three cells which

54
00:04:20,700 --> 00:04:25,500
combined are occuring twice so what I'll 
do is again I'll join my cells

55
00:04:25,600 --> 00:04:30,400
using testjoin delimiter to than I'll select 
this range and press enter

56
00:04:30,500 --> 00:04:35,300
so my cells are combined now we will use 
conditional formatting in this

57
00:04:35,400 --> 00:04:40,200
combined cell I'll select my range conditional 
formatting highlight cells and duplicate 
values

58
00:04:40,300 --> 00:04:45,100
you can always customize if you want to highlight 
these in red color

59
00:04:45,200 --> 00:04:50,000
yellow green or you can also choose a custom 
color border or any kind of format

60
00:04:50,100 --> 00:04:54,900
you like so here we will keep it default 
and press ok so these are the entries

61
00:04:55,000 --> 00:04:59,800
which are occuring twice or more than two 
times in my data now the next

62
00:04:59,900 --> 00:05:04,700
method is using the advance filters and go 
to data I have an option here

63
00:05:04,800 --> 00:05:09,600
advanced which I belive most of you must 
not have used it

64
00:05:09,700 --> 00:05:14,500
click on advcanced now here I have two options 
filter the list in place

65
00:05:14,600 --> 00:05:19,400
or copy to another location we will try both 
the options in this video

66
00:05:19,500 --> 00:05:24,300
first of all I am using filter the list in 
place and I am selecting this range

67
00:05:24,400 --> 00:05:29,200
which is already selected you can see so 
no need to change it now I am just clicking 
on this

68
00:05:29,300 --> 00:05:34,100
unique records only and press ok now you 
will notice my duplicate

69
00:05:34,200 --> 00:05:39,000
entries are hidden it lies somewhwere between 
the row 32 and 36

70
00:05:39,100 --> 00:05:43,900
only my unique values can be seen now I'll 
select this again press

71
00:05:44,000 --> 00:05:48,800
Alt semicolumn cnrtl+c and paste here cntrl+v 
so

72
00:05:48,900 --> 00:05:53,700
you will see that all my unique values are 
copied now lets select the second option

73
00:05:53,800 --> 00:05:58,600
I am deleting this table I'll click on name 
go to data advanced now

74
00:05:58,700 --> 00:06:03,500
I am using the option of copy to another 
location I'll click copy to another location 
choose my

75
00:06:03,600 --> 00:06:08,400
range which is already selected copy to let 
me give this a new location

76
00:06:08,500 --> 00:06:13,300
lets say its F22 unique records only and 
ok now this has copied only

77
00:06:13,400 --> 00:06:18,200
the unique entries in my data set to my new 
given range now the last

78
00:06:18,300 --> 00:06:23,100
option is the default option which most of 
us are using I'll select my range

79
00:06:23,200 --> 00:06:28,000
and you have this option remove duplicates 
under data tools I'll click on

80
00:06:28,100 --> 00:06:32,900
this this will ask for a confirmation that 
do you want to remove your duplicates

81
00:06:33,000 --> 00:06:37,800
from all the columns or from a single coulmns 
since these columns are

82
00:06:37,900 --> 00:06:42,700
interconnected with each other so I'll select 
all these columns and press ok

83
00:06:42,800 --> 00:06:47,600
so it has removed my three duplicate values 
and it has found ten unique

84
00:06:47,700 --> 00:06:52,500
names I hope you like this video please do 
not forget to like comment and share

85
00:06:52,600 --> 00:06:53,900
thank you for watching guys