How to Remove Duplicate Rows Based on One Column in Excel

In Microsoft Excel, it’s so usual to find the necessity to remove duplicate rows based on one column. We can insert formulas, apply featured tools or use VBA codes to serve the purposes. In this article, you’ll get to know all the quickest techniques to delete duplicate rows based on a single column only with proper examples and illustrations.


Download Practice Workbook

You can download the following Excel book that we’ve used to prepare this article.


3 Suitable Methods to Remove Duplicate Rows Based on One Column in Excel

1. Use ‘Remove Duplicates’ Tool in Excel Spreadsheet

Let’s get introduced to the dataset first. The following table or chat is representing some detailed data for a charity fund. The corresponding columns are lying with several names of donors, their donation amounts, donation dates, and the mediums of their donations.

Use ‘Remove Duplicates’ Tool in Excel Spreadsheet

What we’ll do here is remove duplicate rows based on the donor names only. It means we’ll filter the donor names and extract each unique name along with the corresponding rows from their first occurrences only.

📌 Step 1:

➤ Select the entire table first.

➤ Under the Data tab or ribbon, choose the Remove Duplicates tool from the Data Tools drop-down.

Use ‘Remove Duplicates’ Tool in Excel Spreadsheet

📌 Step 2:

➤ A dialog box will appear. From the Columns options, put a check on the Donor and leave other options unmarked.

➤ Press OK.

Use ‘Remove Duplicates’ Tool in Excel Spreadsheet

And you’ll find the following outputs with a pop-up message showing the status of the return values.

Use ‘Remove Duplicates’ Tool in Excel Spreadsheet

Click OK and you’re now seeing the filtered data with all duplicate rows erased.

Use ‘Remove Duplicates’ Tool in Excel Spreadsheet

Read more: How to Remove Duplicate Rows in Excel


2. Apply Filter Options to Remove Duplicates Based on One Column

Now we’ll use the COUNTIF function to find the number of duplicates based on a condition and the outputs will be shown under the Duplicates header in Column F. Then we’ll apply the Filter options in all headers of the data table and filter out the duplicate rows depending on the outputs from the COUNTIF function.

Apply Filter Options to Remove Duplicates Based on One Column

📌 Step 1:

➤ In Cell F5, type the following formula:

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

Apply Filter Options to Remove Duplicates Based on One Column

📌 Step 2:

➤ Press Enter and you’ll get the first output.

➤ Use the Fill Handle to drag down the entire column and the values more than ‘1’ will be counted as duplicates.

Apply Filter Options to Remove Duplicates Based on One Column

📌 Step 3:

➤ Now select the entire table.

➤ Under the Home tab, choose the Filter command from the Sort & Filter drop-down in the Editing group of commands.

Apply Filter Options to Remove Duplicates Based on One Column

We’ll have the filter buttons assigned for all headers in the following data table.

Apply Filter Options to Remove Duplicates Based on One Column

📌 Step 4:

➤ Click on the drop-down in the Duplicates header and the filter options for the corresponding column will open up.

➤ Under the Select All tab, unmark the option ‘1’.

➤ Press OK and you’re done.

Apply Filter Options to Remove Duplicates Based on One Column

You’ll find the duplicate rows as shown below.

Apply Filter Options to Remove Duplicates Based on One Column

📌 Step 5:

➤ Now delete all duplicate rows containing the entire data.

Apply Filter Options to Remove Duplicates Based on One Column

📌 Step 6:

➤ Open up the filter options again from the Duplicates header in Column F.

➤ Put a check on the option ‘1’ only.

➤ Press OK for the last time.

Apply Filter Options to Remove Duplicates Based on One Column

Finally, you’ll get all the unique rows as displayed in the picture below.  Now you can remove the filter buttons from the headers and the resultant data will be the same as we’ve already deleted the duplicate rows.

Apply Filter Options to Remove Duplicates Based on One Column

Read more: How to Remove Duplicates Based on Criteria in Excel


3. Run VBA Codes to Remove Duplicates Based on One Column in Excel

In our final method, we’ll insert some VBA codes to define a macro that will remove duplicates based on the first column.

📌 Step 1:

➤ Right-click on the Sheet name (Sheet3) first and you’ll find some Sheet options.

➤ Select View Code.

A VBA window will appear where we’ll have to insert the codes.

Run VBA Codes to Remove Duplicates Based on One Column in Excel

📌 Step 2:

➤ In the VBA window, paste the following codes:

Option Explicit
Sub Remove_Duplicate_Rows()
Dim Rng As Range
Set Rng = Selection
Rng.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

Run VBA Codes to Remove Duplicates Based on One Column in Excel

📌 Step 3:

➤ Return to your Excel sheet now.

➤ Select the entire data table.

➤ From the Developer tab, press the Macros command.

Run VBA Codes to Remove Duplicates Based on One Column in Excel

📌 Step 4:

➤ In the Macro window, the Macro name will be assigned automatically.

➤ Click Run and you’re done with the steps.

Run VBA Codes to Remove Duplicates Based on One Column in Excel

Like in the screenshot below, we’ll find the unique rows only based on the first column. And the duplicate rows will disappear right away.

Run VBA Codes to Remove Duplicates Based on One Column in Excel

Read more: How to Remove Duplicates in Excel Using VBA


Concluding Words

I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when you have to remove duplicate rows and find unique rows only. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo