In this lesson I will explain you how to remove duplicates in excel. We often deal with excel or csv files which contain duplicate values. Sometimes duplicate values add meaning to the data. But sometimes these duplicates are troublesome. Specially when we analyze data. Now I will explain step by step, how to remove duplicates from your excel or csv files. Let’s consider this sample excel file. As you can see, I have highlighted the duplicates in this excel sheet.
To remove duplicates, first click on one cell inside the data range. Then go to the “Data” tab and click on “Remove Duplicates”
“Remove Duplicates” dialog box will pop like this.
Select all the checkboxes and click OK. You will get result like this.
Also we can do that using a vba macro as well. Below is the code to do that.
|
Sub RemoveDuplicates() ActiveSheet.Range("$A$1:$C$20").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes End Sub |
In above example, values will be removed only if columns A,B and C all have same data. Next let’s learn how to remove rows if a particular column has same data. Let’s consider this sample data set.
In this example sheet, I have highlighted the duplicate values we can find in column A. As you can see if we consider both column A and B then data are not same. In this example we are going to remove duplicates only based on column A. To do that first click on one cell within the data set. And then go to “Data” tab like in previous example and click on “Remove Duplicates”. Then remove duplicates dialog box will appear. In this dialog box only put tick to the “First Name” checkbox.
Then click OK and you will get this result.
Also you can use below macro to do it automatically as well.
|
Sub RemoveDuplicatesFromColA() ActiveSheet.Range("$A$1:$B$17").RemoveDuplicates Columns:=1, Header:=xlYes End Sub |