Alok Paul
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 7 easy ways to delete duplicates but keep one value in Excel. Deleting all duplicates is a bit easier task. But we need some extra returns and that will be discussed here.
In the following GIF, you can easily notice how we are deleting duplicates in Excel but keeping one. Furthermore, in the following article, we will describe how you can do the task smoothly.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
7 Methods to Delete Duplicates But Keep One Value in Excel
You can see the Country, Name, and Position columns in the following dataset. Furthermore, using this dataset, we will go through 7 different methods to delete duplicates and keep one value in Excel. Here, we used Excel 365. You can use any available Excel version.
1. Use of Advanced Filter Feature to Delete Duplicates Except for One Value
In this method, we will use the Advanced filter feature to delete duplicates but keep one value.
Steps:
- First, select the cells where we will check duplicates.
- Here, we selected cells B4:B11.
- Then, go to Data from the main tab.
- Now, click the Sort & Filter feature.
- After that, we will get the Advanced.
- At this moment, an Advanced Filter dialog box will appear.
- We want to see the country names in another column, so select Copy to another location.
- Afterward, select the location on the Copy to box.
- Here, we selected cells F4:F11 in the Copy to box.
- Then, select Unique records only.
- Finally, click OK.
Therefore, from the range of cells F5:F8, we see that duplicates are removed and only one is kept.
Related Content: How to Remove Duplicates Based on Criteria in Excel (4 Methods)
2. Applying Filter Feature
In this method, we will use the Filter tool to delete duplicates but keep one value.
Steps:
- First of all, we will select cell B5:B11 >> go to the Data tab >> select Editing.
- Then, from the Sort & Filter group >> select Sort A to Z.
- At this moment, a Sort Warning will appear.
- Then, make sure Expand the selection is marked.
- In addition, click Sort.
- Therefore, we get the data in ascending order.
- After that, we added the Test column.
- Moreover, go to Cell E5 of the Test column.
- Furthermore, compare the cells of column Country by typing the following formula in cell E5.
=B5=B6
- After that, press ENTER.
- Therefore, you can see the result in cell E5.
- Furthermore, we will drag down the formula with the Fill Handle tool.
- Hence, you can see the complete Test column.
- Now, to apply the Filter select the range B4:E4.
- Then, go to the Home tab.
- After that, select Data from the main tab.
- Then, select the Sort & Filter feature.
- Finally, select Filter.
- Here, you can add the Filter icon by typing CTRL+SHIFT+L.
- Afterward, click on the Filter icon of the Test column.
- Then, unmark FALSE.
- In addition, press OK.
- Hence, we get only the TRUE in the Test column.
- Furthermore, delete the Country names.
- To do so, select the Country names >> press DELETE.
- Therefore, duplicate Country names have been deleted.
- Afterward, remove the filter from our data range by pressing Ctrl+Shift+L.
- As a result, we deleted the duplicates except for the unique values.
Related Content: How to Remove Duplicates and Keep the First Value in Excel (5 Methods)
3. Employing Remove Duplicates Tool
In this method, we will employ the Remove Duplicates tool to delete duplicates but keep one value.Â
Steps:
First, we copy the Country column to column F to apply the Remove Duplicates Tool.
- After that, select the data of column F.
- Then, go to the Data tab.
- Afterward, from Data Tools >> select Remove Duplicates.
- At this point, a Remove Duplicates dialog box will pop up.
- Then, click OK.
- At this point, a new Pop-Up will show how many duplicates are removed and how many unique ones are remaining.
- Then, press OK.
- Finally, we get one country name from the duplicates.
4. Applying VBA Code to Delete Duplicates Without Unique Values in Excel
In this method, we will apply VBA Code to delete duplicates and keep only one unique value.
Steps:
- In the beginning, we will copy the Country column on column F to apply for the VBA.
- After that, go to the Developer tab >> select Visual Basic.
- This will bring out a VBA Editor window.
- Here, you can also press ALT+F11 to bring the VBA Editor window.
- Then, from the Inert tab >> select Module.
- Then, we will type the following code in the Module.
Sub Remove_Deplicates_but_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 Sub
- This program will remove duplicates from column F. F5:F means it will search in that range.
- Then, we will Save the code >> and go back to our Worksheet.
- After that, from the Developer tab >> select Macros.
Then, a Macros dialog box will appear.
After that, we will select Sub >> click on Run.
- As a result, this VBA operation removes all the duplicates and keeps one of each.
- For a better understanding, look at the following GIF.
Related Content: How to Remove Duplicates in Excel Using VBA (3 Quick Methods)
5. Use of Pivot Table Feature to Remove Duplicates
In this method, we will use the Pivot Table feature to delete duplicates in Excel while keeping one value.
Steps:
- First of all, select the data from Column B.
- Then, go to Insert from the main tab.
- Afterward, select the Pivot Table from the commands.
- In addition, select From Table/Range.
- At this point, a dialog box will appear to create the Pivot Table.
- After that, we will select the Existing Worksheet to report Pivot Table.
- Then, in the Location select Cell F4.
- Finally, click OK.
- At this point, from the PivotTable Fields, select Country.
- Therefore, on the main sheet, we will list the country after deleting the duplicates.
6. Inserting Power Query
In this method, we will use the Power Query to delete duplicates in Excel.
Steps:
- First of all, from Column B select the data first.
- Then, go to Data from the Home tab.
- After that, select From Table/Range.
- Then, we will get a Create Table dialog box.
- Then, select My table has headers.
- After that, press OK.
- Moreover, right-click on the Country.
- Then, from the selection tab select Remove Duplicates.
- Finally, we will get the return.
- After that, go to the Home tab >> select Close & Load >> select Close & Load To.
- At this point, an Import Data dialog box will appear.
- Then, select Existing worksheet >> select a location.
- Here, we select cell F4 as the location.
- Then, click OK.
- Therefore, you can only see the unique values in column F.
Related Content: Excel Formula to Automatically Remove Duplicates (3 Quick Methods)
7. Applying Formula to Delete Duplicates While Keeping One Value
In this method, we will use a formula to delete the duplicates in Excel.
Steps:
- In the first place, in Cell C5 write the formula using the COUNTIFS function.
=COUNTIFS($B$5:B5,B5)
- Afterward, press ENTER.
- Therefore, you can see the result in cell C5.
- Then, we will pull the Fill Handle till Cell C11.
- Therefore, you can see the complete Occurance column.
- Moreover, type CTRL+SHIFT+L to add a filter.
- Hence, you can see the Filter icon in the headings.
- After that, from the filter option of cell C4, remove 1 and select the rest of the options.
- Then, press OK.
- After that, we will get the country names except for the 1st occurrence.
- Now, delete all the country names.
- Afterward, disable the filter option by pressing CTRL+SHIFT+L.
- Therefore, you can see the unique country names.
Practice Section
You can download the above Excel file and practice the explained methods.
Conclusion
In this article, we’ve shown 7 methods to delete duplicates but keep one value in Excel. 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. You can visit our website Exceldemy to explore more.
