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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview GIF 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.

Dataset to delete duplicates except for one value in Excel


1. Using Advanced Filter Feature to Delete Duplicates But Keep One Value in Excel

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.

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

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

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


2. Applying Filter Feature to Delete Duplicates But Keep One Value

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.

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

  • At this moment, a Sort Warning will appear.
  • Then, make sure Expand the selection is marked.
  • In addition, click Sort.

Expanding the selection in the Sort Warning Dialog box

  • Therefore, we get the data in ascending order.

Outcome after using the Sort and Filter option

  • 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

Using a Formula in the Test column

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

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

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

  • Afterward, click on the Filter icon of the Test column.
  • Then, unmark FALSE.
  • In addition, press OK.

Unmarking FALSE in the Filter dialog box

  • Hence, we get only the TRUE in the Test column.
  • Furthermore, delete the Country names.
  • To do so, select the Country names >> press DELETE.

Complete Country column

  • Therefore, duplicate Country names have been deleted.
  • Afterward, remove the filter from our data range by pressing Ctrl+Shift+L.

Deleting Country Names

  • As a result, we deleted the duplicates except for the unique values.

Country Column with Unique Values only

Read More: How to Remove Duplicate Names in Excel


3. Using Excel Remove Duplicates Tool to Remove Duplicates and Keep One Value

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.

Copying Country column to column F

  • After that, select the data of column F.
  • Then, go to the Data tab.
  • Afterward, from Data Tools >> select Remove Duplicates.

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

  • At this point, a Remove Duplicates dialog box will pop up.
  • Then, click OK.

Clicking OK in the Remove Duplicates Dialog Box

  • At this point, a new Pop-Up will show how many duplicates are removed and how many unique ones are remaining.
  • Then, press OK.

Clicking OK in the Warning box

  • Finally, we get one country name from the duplicates.

Country Column with Unique values only


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.

Copying Country column to column F for employing VBA Code

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

Using the Developer tab to select Visual Basic

  • Then, from the Inert tab >> select Module.

Selecting Module from the Insert tab

  • 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

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.
  • Then, we will Save the code >> and go back to our Worksheet.
  • After that, from the Developer tab >> select Macros.

Use of Developer tab to select Macros

Then, a Macros dialog box will appear.

After that, we will select Sub >> click on Run.

Running the Code

  • As a result, this VBA operation removes all the duplicates and keeps one of each.

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

  • For a better understanding, look at the following GIF.

GIF to show VBA code result


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

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.

Inserting Pivot Table

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

Selecting items in the PivotTable form table or range dialog box

  • At this point, from the PivotTable Fields, select Country.

Selecting Country

  • Therefore, on the main sheet, we will list the country after deleting the duplicates.

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


6. Inserting Power Query to Delete Duplicates But Keep One Value

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.

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

  • Then, we will get a Create Table dialog box.
  • Then, select My table has headers.
  • After that, press OK.

Selecting Items in Create Table dialog box

  • Moreover, right-click on the Country.
  • Then, from the selection tab select Remove Duplicates.

Selecting Remove Duplicates from Power Query

  • Finally, we will get the return.
  • After that, go to the Home tab >> select Close & Load >> select Close & Load To.

Selecting the Close & Load To option

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

Selecting Items in the Import Data Dialog Box

  • Therefore, 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


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

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)

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

  • Afterward, press ENTER.
  • Therefore, you can see the result in cell C5.
  • Then, we will pull the Fill Handle till Cell C11.

Pulling down the formula with the Fill Handle tool

  • Therefore, you can see the complete Occurance column.

Complete Occurance column

  • Moreover, type CTRL+SHIFT+L to add a filter.
  • Hence, you can see the Filter icon in the headings.

Adding Filter Icon

  • After that, from the filter option of cell C4, remove 1 and select the rest of the options.
  • Then, press OK.

Unmarking 1

  • After that, we will get the country names except for the 1st occurrence.
  • Now, delete all the country names.

The country column with duplicate value

  • Afterward, disable the filter option by pressing CTRL+SHIFT+L.
  • Therefore, 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 exercise while you are reading this article.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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