How To Use Formula To Remove Duplicate Rows In Excel

In the previous article, I told you how to use the excel advanced filter and the remove duplicate menu to remove duplicate rows in excel. In this article, I will tell you how to use a formula to filter out duplicate rows in excel and remove them.

1. How To Use Formula To Remove Duplicate Rows In Excel.

  1. We use the below data example, there are duplicate rows in it.
  2. Now we will filter out the rows in which columns C, D, and E contain duplicate data, which means when 2 rows contain the same data in columns C, D, and E, the 2 rows are treated as duplicates.
  3. Input the formula = C2&D2&E2 in cell F2, then autofill the formula in column F until the last row.
  4. Then it will combine the data in columns C, D, and E and put the result in column F.
  5. Now, input the formula =COUNTIF($F$1:F2, F2) in cell G2, and auto-fill the formula in column G until the last row.
  6. Then it will count and display the number of times the cell data appears in column F, you can see some data appears more than one time.
  7. Select column G and click the excel Data —> Filter menu item to open the Filter dialog window.
  8. Check the checkbox before the number 1 in the column data list of the Filter area.
  9. When you click the Apply Filter button, it will display the rows that column G contains the number 1 only.
  10. In such a way, it filters out and removes the duplicated rows in the example dataset, you can copy the unique rows and paste them into another excel worksheet.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.