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.

Dataset to delete duplicates except for 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.

Selecting Advanced Filter Option to delete duplicates but keep one value in Excel

  • 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.

Selecting Items from Advanced Filter Dialog Box

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

The result after deleting duplicates and keeping one value using Advanced Filter in Excel

Read More: How to Remove Duplicates but Keep the First Value in Excel


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.

Applying Sort & Filter Feature for deleting duplicates and keeping one value by using Formula in Excel

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

Expanding the selection in the Sort Warning Dialog box

  • We get the data in ascending order.

Outcome after using the Sort and Filter option

  • 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

Using a Formula in the Test column

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

Applying 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.

Employing Filter Feature to delete duplicates except for one value in Excel

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

Unmarking FALSE in the Filter dialog box

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

Complete Country column

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

Deleting Country Names

  • We deleted the duplicates except for the unique values.

Country Column with Unique Values only

Read More: How to Remove Duplicate Names in Excel


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.

Copying Country column to column F

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

Selecting the Remove Duplicates Tool to delete duplicates but keeping one value in Excel

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

Clicking OK in the Remove Duplicates Dialog Box

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

Clicking OK in the Warning box

  • We get one country name from the duplicates.

Country Column with Unique values only


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.

Copying Country column to column F for employing VBA Code

  • 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.

Using the Developer tab to select Visual Basic

  • From the Inert tab, select Module.

Selecting Module from the Insert tab

  • 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

Typing Code for deleting duplicates and keeping one using Formula in Excel

This program will remove duplicates from column F. F5:F means it will search in that range.

  • Save the code and go back to our Worksheet.
  • From the Developer tab, select Macros.

Use of Developer tab to select Macros

  • A Macros dialog box will appear.  Select the Sub and click on Run.

Running the Code

  • This VBA function removes all the duplicates and keeps one of each.

The outcome after deleting duplicates and keeping one using VBA Code in Excel

  • Look at the following GIF for demonstration.

GIF to show VBA code result


Method 5 – Using Excel Pivot Table Feature to Remove Duplicates But Keep One Value 

Steps:

  • Select the data from Column B.
  • Go to Insert from the main tab.
  • Select the Pivot Table from the commands.
  • Choose From Table/Range.

Inserting Pivot Table

  • A dialog box will appear to create the Pivot Table.
  • Select the Existing Worksheet for the Pivot Table.
  • In Location, select Cell F4.
  • Click OK.

Selecting items in the PivotTable form table or range dialog box

  • From the PivotTable Fields, select Country.

Selecting Country

  • On the main sheet, this lists the country after deleting the duplicates.

The outcome after deleting duplicates and keeping one using Pivot Table in Excel


Method 6 – Inserting Power Query to Delete Duplicates But Keep One Value

Steps:

  • Select the data you want to filter.
  • Go to Data from the Home tab.
  • Select From Table/Range.

Inserting Power Query for deleting duplicates and keeping one using Formula in Excel

  • This opens a Create Table dialog box. Select My table has headers.
  • Press OK.

Selecting Items in Create Table dialog box

  • Right-click on Country.
  • From the selection tab, select Remove Duplicates.

Selecting Remove Duplicates from Power Query

  • Go to the Home tab, select Close & Load, and choose Close & Load To.

Selecting the Close & Load To option

  • An Import Data dialog box will appear. Select Existing worksheet and pick a location. We selected cell F4 as the location.
  • Click OK.

Selecting Items in the Import Data Dialog Box

  • You can only see the unique values in column F.

The outcome after deleting duplicates and keeping one value using a power query in Excel

Read More: How to Remove Duplicate Rows Except for 1st Occurrence in Excel


Method 7 – Applying COUNTIFS Function to Delete Duplicates While Keeping One Value in Excel

Steps:

  • In Cell C5 copy this formula:

=COUNTIFS($B$5:B5,B5)

Applying COUNTIFS Function for deleting duplicates and keeping one using Formula in Excel

  • Press Enter.
  • Drag the Fill Handle from C5 to C11.

Pulling down the formula with the Fill Handle tool

  • You can see the complete Occurence column.

Complete Occurance column

  • Press Ctrl + Shift + L to add a filter.
  • You can see the Filter icon in the headings.

Adding Filter Icon

  • From the filter option of cell C4, remove 1 and select the rest of the options.
  • Press OK.

Unmarking 1

  • We will get the country names except for the 1st occurrence.
  • Delete all the country names.

The country column with duplicate value

  • Disable the filter by pressing Ctrl + Shift + L.
  • You can see the unique country names.

The outcome after deleting duplicates and keeping one using Formula in Excel


Download Practice Workbook

Download this practice workbook to follow along while reading the article.


Related Articles


<< Go Back to Remove Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo