How to Delete Duplicates But Keep One Value in Excel (7 Methods)

You can see the Country, Name, and Position columns in the following dataset. Using this dataset, we will go through 7 different methods to delete duplicates and keep one value in Excel.

Method 1 – Using Advanced Filter Feature to Delete Duplicates but Keep One Value in Excel

Steps:

Select the cells to check for duplicates. We selected cells B4:B11.
Go to Data from the main tab.
Click the Sort & Filter feature.
Choose Advanced.

An Advanced Filter dialog box will appear. Select Copy to another location.
Select the location on the Copy to box. We selected cells F4:F11.
Select Unique records only.
Click OK.

Therefore, from the range of cells F5:F8, we see that duplicates are removed and only one is kept.

Method 2 – Applying Filter Feature to Delete Duplicates But Keep One Value

Steps:

Select cells B5:B11 and go to the Data tab, then choose Editing.
From the Sort & Filter group, select Sort A to Z.

A Sort Warning will appear. Make sure Expand the selection is marked.
Click Sort.

We get the data in ascending order.

Add the Test column.
Go to Cell E5 of the Test column.
Compare the cells of column Country by copying the following formula in cell E5:

=B5=B6

Press Enter. You can see the result in cell E5.
Drag down the formula with the Fill Handle tool.

You can see the complete Test column. To apply the Filter, select the range B4:E4.
Go to the Home tab.
Select Data from the main tab.
Choose the Sort & Filter feature.
Pick Filter.
Add the Filter icon by typing Ctrl + Shift + L.

Click on the Filter icon of the Test column.
Unmark FALSE.
Press OK.

We get only the TRUE in the Test column.
Delete the duplicate Country names by selecting the Country names and pressing Delete.

Duplicate Country names have been deleted.
Remove the filter from the data range by pressing Ctrl + Shift + L.

We deleted the duplicates except for the unique values.

Method 3 – Using Excel Remove Duplicates Tool to Remove Duplicates and Keep One Value

Steps:

Copy the Country column to column F to apply the Remove Duplicates Tool.

Select the data of column F.
Go to the Data tab.
From Data Tools, select Remove Duplicates.

A Remove Duplicates dialog box will pop up. Click OK.

A new pop-up will show how many duplicates are removed and how many unique ones are remaining. Press OK.

We get one country name from the duplicates.

Method 4 – Applying VBA Code to Delete Duplicates Without Unique Values in Excel

Steps:

Copy the Country column on column F to apply for the VBA.

Go to the Developer tab and select Visual Basic. This will bring out a VBA Editor window. You can also press Alt + F11 to open the VBA Editor window.

From the Inert tab, select Module.

Copy 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