Akib Bin Rashid
We frequently use Microsoft Excel for various purposes. When working with large datasets, removing duplicate rows in Excel is indeed a common task. However, you may not know that you can perform this task in several easy and interesting ways. In this article, I am going to illustrate 7 quick and simple methods on how to remove duplicate rows except 1st occurrence in Excel.
Download Practice Workbook
This is the datasheet for this article. Here, we have listed down the Major Course taken by some students along with their IDs. You will notice that, in the dataset, there are duplicate rows. For instance, row 5, row 9, and row 11 are duplicates. Similarly, row 10 and row 13 are the same.
7 Ways to Remove Duplicate Rows Except 1st Occurrence in Excel
1. Remove Duplicate Rows Except 1st Occurrence Using Remove Duplicates Feature
Suppose you want to remove duplicate rows in your datasheet. You can do it by using Remove Duplicates from the Data tab.
To do so,
First of all, select the entire datasheet. In our example, it is B4:D15.
Then, go to the Data tab >> from Data Tools >> select Remove Duplicates.
Then, the Remove Duplicates dialog box will appear. You will by default have all the columns checked and My data has headers box ticked. Then, select OK.
As a result, it will remove the duplicate rows. Excel will show that to you.
This will be your output.
If you notice, you will see that all the duplicate rows in your initial datasheet have been removed. For example, the row having ID – 1001, Name – Jack, and Major Course – Finance is only in row 5 in your final output whereas it was in multiple rows in the initial datasheet ( Row 5, Row 9, and Row 11).
Read More: How to Remove Duplicate Rows in Excel (3 Ways)
2. Remove Duplicate Rows Except 1st Occurrence Using Advanced Filter
One more way to remove duplicate rows in Excel is to use Advanced Filter. To use this method,
First, select the range. In our case it is B4:D15.
Then, go to the Data tab >> select Advanced Filter.
Advanced Filter window will appear. Then put your range in the List range box and tick the Unique records only box. Then click OK.
The output will be like this.
You’ll observe that Excel has eliminated all of the duplicate rows from your original datasheet. For instance, in your final datasheet, the row with ID – 1001, Name – Jack, and Major Course – Finance is only in row 5, but it was in multiple rows in the original datasheet ( Row 5, Row 9, and Row 11).
Read More: How to Remove Duplicates Based on Criteria in Excel (4 Methods)
3. Remove Duplicate Rows Except 1st Occurrence using UNIQUE Function
A UNIQUE function is basically a function that returns the unique values from a selected range. We can easily use this function to remove duplicate rows in Excel. Now, I will explain how to remove duplicate rows using the UNIQUE function.
First of all, select a cell. In our case it is F4. Then write the following formula,
After that, press ENTER. You will surely notice the duplicate values removed. After some formatting, this is the output in my worksheet. Obviously, you can format it the way you want.
Note: It is worth mentioning that the UNIQUE function is available in the latest versions like Excel 365 and Excel 2021. So if you are using an older version, this method may not work.
Another thing is that, in this method, you can actually remove the duplicates without changing your initial dataset. So it is surely an advantage of this method.
Read More: Excel Formula to Automatically Remove Duplicates (3 Quick Methods)
4. Remove Duplicate Rows Except 1st Occurrence Using Power Query
In this section, I am going to discuss another useful method to remove duplicate rows in Excel. Here, I am going to use Power Query. To apply so,
First, select the entire data range that you are going to use. In our case it is B4:D15.
Then, go to Data tab >> select From Table / Range.
As a consequence, a Create Table window will appear. Simply click OK.
A new window will pop up. We call it the Power Query window.
Select the entire table from that window. Then right click the mouse and select Remove Duplicates.
All the duplicate values will be eliminated. Now you can transfer this table to your original workbook. To do so, simply click Close & Load.
Thereby, you will create a new sheet in the workbook. In our case, it is named as Table5. You can rename it as well as format it as you wish.
Read More: How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)
Similar Readings
- Remove duplicate rows based on two columns in Excel [4 ways]
- How to Remove Duplicate Rows in Excel Table
- Fix: Excel Remove Duplicates Not Working (3 Solutions)
- How to Remove Duplicate Rows Based on One Column in Excel
5. Remove Duplicate Rows Except 1st Occurrence Using COUNTIF Function
You can also remove the duplicate rows in Excel using the COUNTIF function. Though this is somewhat complex, it is always useful to learn something new.
To apply this method, you have to make two new columns in your datasheet. Here, these columns are labeled as Merged and Count.
Then I have written the formula,
Now use the Fill Handle to AutoFill. Thus, you will merge all the cell values.
Now, in the F5 cell, write down the following formula,
Now use the Fill handle to AutoFill up to cell F15.
Now, select the whole dataset again, go to Data tab >> select Sort and Filter >> and select Filter.
A drop-down menu will pop up for every column.
In the Count column, click the drop-down menu and tick the box for 3.
Now all the rows having 3 in the Count column will show up.
Select all the rows except for the first one, and delete them manually.
You have to repeat this task for all the boxes until you have only the box containing 1 after clicking the drop down list.
Finally, your result will be like this.
This means, all the rows are in the table only once. That is, all the duplicate rows have been removed.
Read More: How to Remove Both Duplicates in Excel (5 Easy Ways)
6. Remove Duplicate Rows Except 1st Occurrence Using Conditional Formatting
To use Conditional Formatting in order to remove the duplicate rows,
First, make a new column like one in the method using COUNTIF Function. I have named it Merged.
Then go to Home tab >> select Conditional Formatting >> select New Rule.
A New Formatting Rule window will appear. Then select a Rule Type. In our case, it will be Use a formula to determine which cells to format.
Then write down the formula in the box.
With this formula, you are going to format the cells that are duplicated in your dataset.
After formatting, click OK. The image below shows how I have formatted it in this case.
You will see the duplicate cells are formatted the way you wanted. Then put Filter on for the Merged column.
Now choose Filter by Color and select the red colored box.
After that, all the red colored cells will appear. Then delete the rows the way you deleted in the method using COUNTIF Function. Thus you will remove all the duplicate rows.
7. Remove Duplicate Rows Except 1st Occurrence Using VBA
Now I am going to discuss how to remove duplicate rows using VBA.
To remove duplicate rows,
First, go to the Developer tab >> select Visual Basic.
Then go to Insert tab>>Module
Module window will appear. Then write the following code.
Sub RemoveDuplicates ()
Dim Rng As Range
Set Rng = Range("B4:D15")
Rng.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes
End Sub
Here, this code will create a macro that will remove duplicate rows from the cell range B4:D15. To do so, I’ve created a Sub Procedure RemoveDuplicates, I declared Rng as Range.
Here, I have used the Set method to set the range and also used the VBA RemoveDuplicates method where in the Array given column numbers 1 & 3 to compare two columns’ values so that it only removes duplicates rows.
Then click the Run button.
Thus you will remove all the duplicate rows.
Read More: How to Remove Duplicates in Excel Using VBA (3 Quick Methods)
Practice Workbook
It is simple to remove duplicate rows in Excel. However, practicing is the most important aspect of mastering this skill. This is why I have attached a practice sheet for you to use.
Conclusion
I have tried to explain how to remove duplicate rows except for 1st occurrence in Excel in N different ways in this article. I’ll be glad if it becomes useful to anyone. And lastly, If you have any comments or suggestions, please leave them in the comment section.
Excel with us!
