Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to delete rows based on duplicates in one column in Excel?

We can quickly delete duplicate rows by Data > Remove Duplicates feature in Excel. But do you know how to delete entire rows based on duplicates in a single column in Excel? This article comes up with several easy methods to settle this matter.

Delete rows based on duplicates in one column with Remove Duplicates feature

Delete rows based on duplicates in one column with Kutools for Excel

Delete rows and combine/sum/average/count values in columns based on duplicates in one column

Easily combine and sum rows based on duplicate values in one column in Excel

Kutools for Excel’s Advanced Combine Rows utility can help Excel users to batch sum, count, average, max, min the values in one column based on the criteria in another column easily. Full Feature Free Trial 30-day!
doc sum by criteria in another column 02

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Delete rows based on duplicates in one column with Remove Duplicates feature

This method will introduce the Remove Duplicates feature to remove entire rows based on duplicates in one column easily in Excel.

1. Select the range you will delete rows based on duplicates in one column, and then click Data > Remove Duplicates.

2. In the popping up Remove Duplicates dialog box, please only check the Column whose duplicate values you will remove entire rows based on, and click the OK button. See screenshot:

3. And then a dialog box will come out and show how many duplicate values have been found and removed. Just click the OK button to close it.

And now entire rows are deleted based on the duplicate values in the specified column. See screenshot:


Delete rows based on duplicates in one column with Kutools for Excel

Sometimes, you may want to delete rows based on all duplicate values in a column, and remain rows with unique values only. In this case, you can try Kutools for Excel’s Select Duplicate & Unique Cells utility.

1. Select the column whose duplicate values you will remove entire rows based on, and click Kutools > Select > Select Duplicate & Unique Cells. See screenshot:

2. 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, check the Select entire rows option, and click the Ok button. See above screenshot:

3. And then a second Select Duplicates & Unique Cells dialog box comes out and shows how many rows based on the duplicates in the selected column have been selected. Click the OK button to close it.

4. Click Home > Delete > Delete Sheet Rows to delete all selected rows with duplicate values. So far you have removed all rows based on the duplicate values in the specified column. See screenshot:

Original range:

If selecting the Duplicates (Except 1st one) option:

If selecting All duplicates (Including 1st one) option:

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Delete rows and combine/sum/average/count values in columns based on duplicates in one column

If you want to delete entire rows based on duplicate values in the specified column, but combine values in other columns based on the duplicates, or just remain the calculation results of summing/ averaging/counting, etc.) in other columns, you can try Kutools for Excel’s Advanced Combine Rows utility.

1. Select the range where you want to delete rows based on duplicates in one column, and click Kutools > Content > Advanced Combine Rows. See screenshot:

2. In the opening Advanced Combine Rows dialog box, please do as follows:
(1) In the Column section, select the column which contains the duplicate values, and click the Primary Key;
(2) It’s optional. In the Column section, select the column where you will combine values based on duplicates in the specified column, click Combine and then specify a separator from the drop down list. In our case we select the Price column and click the Combine > Comma.
(3) It’s optional. In the Column section, select the column where you will calculate (sum, average, count, etc.) values based on duplicates in the specified column, click the Calculate and then select a calculation type from the drop down list. In our case we select the Amount column and click the Calculate > Sum.

3. 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. See screenshot:

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Demo: delete rows based on duplicates in one column in Excel

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Related articles:


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    kellytte · 8 months ago
    Hi gwen,
    The first method by Remove Duplicates feature, will remove rows from the specified range containing the column.
    If you need to remove the entire rows from the whole worksheet, the Select Duplicate & Unique Cells feature of Kutools for Excel is recommended.
  • To post as a guest, your comment is unpublished.
    gwen · 9 months ago
    the first section is incorrect. When "Removing Duplicates" and only selecting the column you wish to remove duplicates from, the remaining cells are moved up. The row is not deleted!
  • To post as a guest, your comment is unpublished.
    master · 10 months ago
    u really helped me