Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

Remove Duplicate Rows Except for 1st Occurrence in Excel


Remove Duplicate Rows Except for 1st Occurrence in Excel: 7 Ways


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.

Remove Duplicate Rows Except for 1st Occurrence in Excel

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.

Remove Duplicate Rows Except for 1st Occurrence in Excel

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 Names in Excel 


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.

Remove Duplicate Rows Except for 1st Occurrence in Excel

Advanced Filter window will appear. Then put your range in the List range box and tick the Unique records only box. Then click OK.

Remove Duplicate Rows Except for 1st Occurrence in Excel

The output will be like this.

Remove Duplicate Rows using Advanced Filter

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 but Keep the First Value in Excel 


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,

=UNIQUE(B4:D15)

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.

Remove Duplicate Rows using UNIQUE function

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 


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.

emove Duplicate Rows Except 1st Occurrence Using Power Query

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.

Remove Duplicate Rows Except for 1st Occurrence in Excel

Select the entire table from that window. Then right click the mouse and select Remove Duplicates.

Remove Duplicate Rows Except for 1st Occurrence in Excel

All the duplicate values will be eliminated. Now you can transfer this table to your original workbook. To do so, simply click Close & Load.

Remove Duplicate Rows using Power Query

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.

Remove Duplicate Rows Except for 1st Occurrence in Excel

Read More: How to Delete Duplicates But Keep One Value 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,

=B5&” “&C5&” “&D5
I have used this formula to merge the values of cells B5, C5, and D5. “ ” is used to put a space among these cell values. Therefore, when I press ENTER, 1001 Jack Finance will appear in cell E5.

Remove Duplicate Rows Except for 1st Occurrence in Excel

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,

=COUNTIF($E$5:$E$15,E5)
This formula will tell you how many times a particular cell value appears. For example, when you press ENTER, cell F5 will show 3 in return. This is because the cell value in E5 ( that is, 1001 Jack Finance) is in the range E5:E11 three times (check E5, E9, E11).

Remove Duplicate Rows Except for 1st Occurrence in Excel

Now use the Fill handle to AutoFill up to cell F15.

Remove Duplicate Rows using COUNTIF function

Now, select the whole dataset again, go to Data tab >> select Sort and Filter >> and select Filter.

Remove Duplicate Rows Except for 1st Occurrence in Excel

A drop-down menu will pop up for every column.

Remove Duplicate Rows Except for 1st Occurrence in Excel

In the Count column, click the drop-down menu and tick the box for 3.

Remove Duplicate Rows Except for 1st Occurrence in Excel

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.

Remove Duplicate Rows Except for 1st Occurrence in Excel

You have to repeat this task for all the boxes until you have only the box containing 1 after clicking the drop down list.

Remove Duplicate Rows Except for 1st Occurrence in Excel

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.


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.

Remove Duplicate Rows Except for 1st Occurrence in Excel using Conditional Formatting

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.

=COUNTIF($E$5:$E5,$E5)>1
Then format as you wish.
With this formula, you are going to format the cells that are duplicated in your dataset.

Remove Duplicate Rows Except for 1st Occurrence in Excel

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.

Remove Duplicate Rows Except for 1st Occurrence in Excel

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.

Remove Duplicate Rows Except for 1st Occurrence in Excel by Conditional Formatting


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.

Remove Duplicate Rows Except for 1st Occurrence in Excel using VBA

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

Remove Duplicate Rows using VBA

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.

Remove Duplicate Rows Except for 1st Occurrence in Excel

Thus you will remove all the duplicate rows.


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.

Remove Duplicate Rows Except for 1st Occurrence in Excel


Download Practice Workbook


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!


Related Articles


<< Go Back to Remove Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo