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

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.

Data set for how to delete duplicates in Excel but keep one


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.

Delete Duplicates Using Advanced Sort & Filter in Excel

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.

Delete Duplicates Using Advanced Sort & Filter in Excel

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.

Delete Duplicates Using Advanced Sort & Filter in Excel

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.

Apply Filter Tool to Remove Repetitions but Keep One in Excel

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.

Apply Filter Tool to Remove Repetitions but Keep One in Excel

Step 2:

  • Select Expand the selection.
  • Click Sort.

Apply Filter Tool to Remove Repetitions but Keep One in Excel

Step 3:

  • We get the data in ascending order.

Apply Filter Tool to Remove Repetitions but Keep One in Excel

Step 4:

  • Go to Cell E5 of the Test Column.
  • Compare the cells of Column Country. Like:
=B5=B6

Apply Filter Tool to Remove Repetitions but Keep One in Excel

Step 5:

  • Now, press Enter.
  • Pull the Fill Handle till the Cell E11.

Apply Filter Tool to Remove Repetitions but Keep One in Excel

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.

Apply Filter Tool to Remove Repetitions but Keep One in Excel

Step 7:

  • Now, from the Test Column filter options select TRUE.
  • Then press OK.

Apply Filter Tool to Remove Repetitions but Keep One in Excel

Step 8:

  • We get only the TRUE data here.

Apply Filter Tool to Remove Repetitions but Keep One in Excel

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.

Use Excel Remove Duplicates Tool to Keep the First Instance Only

Step 1:

  • Select the data of Column F.

Use Excel Remove Duplicates Tool to Keep the First Instance Only

Step 2:

  • Go to the Home tab.
  • Select Data from the main tab.
  • Select the Data Tolls command.
  • Now, get the Remove Duplicates option.

Use Excel Remove Duplicates Tool to Keep the First Instance Only

Step 3:

  • We will see the new Pop-Up.
  • Select Country from the box.

Use Excel Remove Duplicates Tool to Keep the First Instance Only

Step 4:

  • Press OK on the Remove Duplicates Pop-Up.

Use Excel Remove Duplicates Tool to Keep the First Instance Only

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.

Excel VBA to Erase Duplicates but Retain the First One

Step 2:

  • Press Alt+F11.
  • We will get a new window to write the VBA code.

Excel VBA to Erase Duplicates but Retain the First One

Step 3:

  • Now write the below code to the window.

Excel VBA to Erase Duplicates but Retain the First One

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 Sub

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

Pivot Table to Remove Duplications While Keeping One in Excel

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.

Pivot Table to Remove Duplications While Keeping One in Excel

Step 3:

  • Now, from the PivotTable Fields select Country.

Pivot Table to Remove Duplications While Keeping One in Excel

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.

Delete Duplicates with Excel Power Query but Conserve the First One

Step 2:

  • We will get a dialog box.
  • Select My table has headers.
  • Then press OK.

Delete Duplicates with Excel Power Query but Conserve the First One

Step 3:

  • On right-click on the Country bar.
  • From the selection tab select Remove Duplicates.

Delete Duplicates with Excel Power Query but Conserve the First One

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.

Excel Formula to Erase Duplicates but Keep One

Step 1:

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

Excel Formula to Erase Duplicates but Keep One

Step 2:

  • Now, press Enter.

Excel Formula to Erase Duplicates but Keep One

Step 3:

  • Pull the Fill Handle till the Cell C11.

Excel Formula to Erase Duplicates but Keep One

Step 4:

  • Now, type Ctrl+Shift+L to add a filter.

Excel Formula to Erase Duplicates but Keep One

Step 5:

  • From the filter option of Cell C4, remove 1 and select the rest options.
  • Then press OK.

Excel Formula to Erase Duplicates but Keep One

Step 6:

  • Now, we will get the country names except the 1st occurrence.

Excel Formula to Erase Duplicates but Keep One

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.


Related Articles

Alok

Hello, this is Alok. I working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo