Nehad Ulfat
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.
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.
📌 Step 2:
➤ A dialog box will appear. From the Columns options, put a check on the Donor and leave other options unmarked.
➤ Press OK.
And you’ll find the following outputs with a pop-up message showing the status of the return values.
Click OK and you’re now seeing the filtered data with all duplicate rows erased.
Read More: How to Remove Duplicates in Excel Sheet (7 Methods)
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.
📌 Step 1:
➤ In Cell F5, type the following formula:
=COUNTIF($B$5:$B5,B5)
📌 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.
📌 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.
We’ll have the filter buttons assigned for all headers in the following data table.
📌 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.
You’ll find the duplicate rows as shown below.
📌 Step 5:
➤ Now delete all duplicate rows containing the entire data.
📌 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.
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.
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.
📌 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
📌 Step 3:
➤ Return to your Excel sheet now.
➤ Select the entire data table.
➤ From the Developer tab, press the Macros command.
📌 Step 4:
➤ In the Macro window, the Macro name will be assigned automatically.
➤ Click Run and you’re done with the steps.
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.
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.
