Alok
Excel is one of the widely used tools we use for our office and business. For most of those works, we need to deal with a large amount of data. Sometimes we need to find unique information from those data. So, in this article, we will discuss how to delete duplicates in Excel but keep one. To delete all duplicates is a bit easier task. But we need some extra returns and that will be discussed here.
For this, we take data from a software company where engineers are from different countries. Here, we will duplicate country names and will keep only one from them.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
7 Methods to Delete Duplicates in Excel but Keep One
We will discuss 7 different methods about how to delete duplicates and keep one in Excel. We tried to use easy images to make all the methods easier.
1. Delete Duplicates Using Advanced Sort & Filter in Excel
We will use the Advanced Sort & Filter Tool to delete duplicates here.
Step 1:
- First, select the cells where we will check duplicates.
- Here we selected Country Column to check for duplicates.
Step 2:
- Go to Home.
- Then go to Data from the main tab.
- Now, click the Sort & Filter command.
- After that, we will get the Advanced option.
Step 3:
- After selecting the Advanced option we will get the Advanced Filter.
- We want to see the country names in another column, so select Copy to another location.
- Now, select the location on the Copy to box.
- Then, select Unique records only.
Step 4:
- Finally, click OK to get the return.
In Column F, we see that duplicates are removed and only one is kept.
2. Apply Filter Tool to Remove Repetitions but Keep One in Excel
We will add a column named Test for applying the Filter tool.
Step 1:
- We select all the data from the Country Column to sort them.
- Click the right button of the mouse.
- From that menu go to Sort.
- Click on Sort A to Z.
Step 2:
- Select Expand the selection.
- Click Sort.
Step 3:
- We get the data in ascending order.
Step 4:
- Go to Cell E5 of the Test Column.
- Compare the cells of Column Country. Like:
=B5=B6
Step 5:
- Now, press Enter.
- Pull the Fill Handle till the Cell E11.
Step 6:
- Now, to apply the Filter select the range B4:E11.
- Go to the Home tab.
- Select Data from the main tab.
- Select the Sort & Filter command.
- Finally, Filter from the given options.
- Or we can type Ctrl+Shift+L.
Step 7:
- Now, from the Test Column filter options select TRUE.
- Then press OK.
Step 8:
- We get only the TRUE data here.
Step 9:
- Now, delete the country names.
Step 10:
- Now, remove the filter from our data range by Ctrl+Shift+L or follow the filter from the previous steps.
3. Use Excel Remove Duplicates Tool to Keep the First Instance Only
First, we copy the Country Column to Column F to apply the Remove Duplicates Tool.
Step 1:
- Select the data of Column F.
Step 2:
- Go to the Home tab.
- Select Data from the main tab.
- Select the Data Tolls command.
- Now, get the Remove Duplicates option.
Step 3:
- We will see the new Pop-Up.
- Select Country from the box.
Step 4:
- Press OK on the Remove Duplicates Pop-Up.
Step 5:
- A new Pop-Up will show how many duplicates are removed and how many unique are remaining.
- Press OK.
Finally, we get one country name from the duplicates.
4. Use Excel VBA to Erase Duplicates but Retain the First One
We will apply VBA to remove duplicates and keep only one unique name.
Step 1:
- Copy the Country Column on Column F to apply for the VBA.
Step 2:
- Press Alt+F11.
- We will get a new window to write the VBA code.
Step 3:
- Now write the below code to the window.
Sub Remove_Deplicates_Keep_One()
Dim CN As Long: CN = Range("F" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("F5:F" & CN).RemoveDuplicates Columns:=1, Header:=Name
End SubThis program will remove duplicates from column F. F5:F means it will search in that range.
Step 4:
- Then press F5 and go back to the previous sheet.
This VBA operation removes all the duplicates and keeps one of each.
5. Apply Pivot Table to Remove Duplications While Keeping One in Excel
We will use the Pivot Table option in this section.
Step 1:
- Select the data from Column B.
- Go to Insert from the main tab.
- Select the Pivot Table from the commands.
Step 2:
- A dialog box will appear to Create Pivot Table.
- We will select the Existing Worksheet to report Pivot Table Data.
- In the Location select Cell F4.
- Then click OK.
Step 3:
- Now, from the PivotTable Fields select Country.
Step 4:
- On the main sheet, we will list the country after deleting the duplicates.
6. Delete Duplicates with Excel Power Query but Conserve the First One
Step 1:
- From Column B select the data first.
- Go to Data from the Home tab.
- Then select From Table/Range.
Step 2:
- We will get a dialog box.
- Select My table has headers.
- Then press OK.
Step 3:
- On right-click on the Country bar.
- From the selection tab select Remove Duplicates.
Step 4:
- Finally, we will get the return.
7. Insert an Excel Formula to Erase Duplicates but Keep One
Here, we will use a formula to delete the duplicates in Excel.
For this first, we copy the Country Column to another sheet and add a Column named Occurrence.
Step 1:
- On Cell C5 write the COUNTIFS function. The formula is:
=COUNTIFS($B$5:B5,B5)
Step 2:
- Now, press Enter.
Step 3:
- Pull the Fill Handle till the Cell C11.
Step 4:
- Now, type Ctrl+Shift+L to add a filter.
Step 5:
- From the filter option of Cell C4, remove 1 and select the rest options.
- Then press OK.
Step 6:
- Now, we will get the country names except the 1st occurrence.
Step 7:
- Now, delete all the country names.
- Disable the filter option by Ctrl+Shift+L.
Conclusion
In this article, we’ve shown 7 methods to how to delete duplicates in Excel but keep one. I hope this will fulfill your needs, as well you can get lots of options. If you have any suggestions please mention them in the comment box.
