How to delete rows based on duplicates in one column in Excel?
In Excel, removing duplicate rows is a straightforward task using the Remove Duplicates feature on the Data tab. However, the process of deleting entire rows based on duplicate values in just one column might not be as immediately obvious. This article introduces several simple methods to efficiently address this scenario, ensuring your data remains accurate and concise.
- Delete rows based on duplicates in one column with Remove Duplicates feature
- Delete rows based on duplicates in one column with Kutools for Excel (Choose to delete all duplicates or keep first instances)
- Delete rows based on duplicates in one colum and combine/sum/average/count values from removed rows
Delete rows based on duplicates in one column with Remove Duplicates feature
This approach focuses on utilizing Excel's Remove Duplicates feature to effortlessly delete entire rows that contain duplicate entries in a specific column.
- Select the range where you will delete rows based on duplicates in one column, and then click Data > Remove Duplicates.
- In the popping up Remove Duplicates dialog box, only check the box next to the column that contains the duplicate values you want to use as a basis for removing entire rows, and click the OK button.
- A subsequent dialog box will display the count of duplicate values found and removed. Click OK to finish and close the dialog box.
Delete rows based on duplicates in one column with Kutools for Excel
With Kutools for Excel’s Select Duplicate & Unique Cells utility, you can also delete rows based on all duplicate values in a column, and remain rows with unique values only. Additionally, this utility offers the flexibility to choose between deleting rows that contain duplicates except their first occurrences, or removing all duplicate rows including the first occurrences.
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!
- Select the column that contains the duplicate values you want to use as a basis for row deletion, and click Kutools > Select > Select Duplicate & Unique Cells.
- In the opening Select Duplicate & Unique Cells dialog box, please check the Duplicates (Except 1st one) option or All duplicates (Including 1st one) option as you need in the Rule section. Then, check the Select entire rows option, and click the Ok button.
- A dialog box as shown below comes out displaying the number of rows that have been selected based on duplicate values in the selected column. Click the OK button to close it.
- Click Home > Delete > Delete Sheet Rows to delete the selected rows with duplicate values. So far you have removed all rows based on the duplicate values in the specified column.
Original data:
Results after removing duplicates:
-
If selecting the Duplicates (Except 1st one) option:
-
If selecting All duplicates (Including 1st one) option:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now
Delete rows based on duplicates in one colum and combine/sum/average/count values from removed rows
If you're looking to delete entire rows based on duplicate values in a specific column, and want to preserve data from these duplicates, please try using Kutools for Excel's Advanced Combine Rows utility. The tools enables you to efficiently remove rows with duplicate entries in a chosen column and simultaneously carry out operations such as combining, summing, averaging, or counting the data from the removed rows in other columns, thereby maintaining the integrity and usefulness of your dataset.
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!
- Select the range where you want to delete rows based on duplicates in one column, and click Kutools > Merge & Split > Advanced Combine Rows.
- In the opening Advanced Combine Rows dialog box, as an example, we will delete rows based on duplicate values in the "Fruit" column. Additionally, we will combine the price information in the "Price" column and sum the total amount for each type of fruit in the "Amount" column. Please follow these steps to execute the process:
- In the Columns list section of the dialog box, locate and select the "Fruit" column, which contains the duplicate values you want to delete rows based on, and then choose Primary Key from the Operation drop-down list.
- Select the "Price" column where we will combine all values associated with the duplicates in the specified column. And then choose a separator from the Operation drop down list under the Combine section.
- Select the "Amount" column where calculations (sum, average, count, etc.) will be applied to values based on duplicates in the specified column. And then choose the Sum option from the Operation drop down list in the Calculate group.
- Click the OK button. And now all rows are deleted based on duplicates in the specified column, and simultaneously other columns are combined or calculated based on the duplicates in the specified column too.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now
Demo: delete rows based on duplicates in one column in Excel
Related articles:
Count duplicate values in a column in Excel
Remove duplicate rows and keep highest values only in Excel
Hide rows if duplicate in Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!