Rifat Hassan
When using Excel with large datasets, one of the most important and widely used tasks is to remove duplicate values from a data set. Today I’ll show you how to remove duplicates from your Excel data set and keep the first value at the same time.
Download Practice Workbook
You can download our practice workbook from here for free!
5 Suitable Methods to Remove Duplicates but Keep the First Value in Excel
Here we’ve got a data set with the Names, IDs, Marks, and Grades of some students in an examination of Sunflower Kindergarten.
Today our objective is to delete the duplicate values while keeping the first values from this data set. Here, be careful. The student’s name, Frank Orwell seems to be a duplicate value. But, the IDs are different. So, these are not duplicate values in the whole dataset.
1. Use Remove Duplicates Feature from Excel Toolbar
The quickest way to remove duplicates from a dataset is to use the Remove Duplicate feature from the Excel toolbar. Go through the steps below to accomplish this.
📌 Steps:
- First, select the whole data set.
- Afterward, go to Data > Remove Duplicates tool in Excel Toolbar under the section Data Tools.
- Subsequently, click on Remove Duplicates.
- Now, put a check on all names of the columns you want to erase duplicates from.
- Subsequently, click on the OK button.
Thus, you will get the duplicates removed automatically from your data set.
Read More: How to Remove Duplicate Names in Excel (7 Simple Methods)
2. Use Advanced Filter to Remove Duplicates and Keep the First Value
You can use the advanced filter of Excel to eliminate duplicates by keeping the first value in your data set. Follow the steps below to do this.
📌 Steps:
- First and foremost, select the whole data set.
- Following, go to Data > Advanced tool in Excel Toolbar under the section Sort & Filter.
- Click on Advanced.
- As a result, the Advance Filter dialogue box will appear.
- In the Advanced Filter dialogue box, put a check on Unique records only.
- Last but not least, click on OK.
Consequently, you will get the duplicate rows removed automatically from your data set.
Read more: How to Remove Duplicate Rows Based on One Column in Excel
3. Insert UNIQUE Function to Remove Duplicates While Keeping the First Value in Excel
You can also use the UNIQUE function of Excel to delete the duplicate values while keeping the first value in Excel. To do this, follow the steps below.
📌 Steps:
- First, click on cell B5 and insert the following formula.
=UNIQUE('Sample Dataset'!B5:E15,FALSE,FALSE)- Subsequently, press the Enter key.
It will delete the rows with duplicate values while keeping the first one, and create a fresh copy of the data set in this new location.
Notes:
- This method is very useful when you want to create a new copy of our data set in a new location.
- The UNIQUE function is available in Office 365 only.
Read More: How to Delete Duplicates in Excel but Keep One (7 Methods)
Similar Readings
- How to Remove Duplicate Rows in Excel Table
- Fix: Excel Remove Duplicates Not Working (3 Solutions)
- How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)
- Remove duplicate rows based on two columns in Excel [4 ways]
4. Use Power Query to Remove Duplicates but Keep the First Entry
You can also use the Power Query tool to achieve your desired target here. Go through the steps below to accomplish this.
📌 Steps:
- At the very beginning, select the whole data set.
- Following, go to Data > From Table / Range tool in Excel Toolbar under the section Get & Transform Data.
- Subsequently, click on From Table / Range.
- Consequently, the Create Table window will appear.
- In the Create Table dialogue box, Put a check on My table has headers.
- Subsequently, click OK.
- As a result, the Power Query Editor will open with your data set.
- Now, hold the Ctrl of your keyboard and select individual column headers.
- Afterward, From the Remove Rows option under the Home tab, click on Remove Duplicates.
- Consequently, the duplicate rows will be removed keeping the first rows.
- Now, under the Home tab, click on the Close & Load button.
- Thus, the filtered table will now be loaded to a sheet in your Excel file. Customize its properties as you want to see the result.
Finally, you will get your desired result which would look like this.
Read More: How to Remove Duplicates from Column in Excel (3 Methods)
5. Embed VBA Codes to Eliminate Duplicates and Keep the First Value
If all the above-mentioned methods can’t satisfy you, you can use a VBA code to remove duplicate rows from your data set.
📌 Steps:
- Open a new VBA window and insert another new module.
- To do this, go to the Developer tab > Visual Basic Tool.
- Afterward, the VB Editor will open.
- Following, click on the Insert tab > choose the Module option.
- Following, Insert the following code in the module and press Ctrl + S.
Code:
Sub Remove_Duplicates()
Dim Rng As Range
Set Rng = Selection
Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub- It produces a Macro called Remove_Duplicates. I want to remove the duplicate rows based on Columns 1 and 2 (Name and ID). You use your one.
- Now, a Microsoft Excel dialogue box will appear.
- Following, click on the No button.
- As a result, the Save As dialogue box will appear.
- Now, choose the Save as type: option as .xlsm file and click on the Save button.
- Thus, your file is macro-enabled now.
- Now, close the VB Editor and select your whole dataset.
- Following, go to the Developer tab > Macros tool.
- As a result, the Macro window will appear.
- Following, choose the Remove_Duplicates macro and click on the Run button.
Thus, the duplicates will be removed and the first values will remain. And, the outcome should look like this.
Here it has not removed Frank Orwell because the IDs of the two students are different, that is, they are two different students.
Read More: Excel VBA: Remove Duplicates from an Array (2 Examples)
How to Identify Duplicates in Excel Without Deleting
Now, say you need to identify the duplicate student IDs without deleting the values. You can do this by conditional formatting. Follow the steps below to do this.
📌 Steps:
- First, select the student IDs cells (cells C5:C15 here).
- Following, go to the Home tab > Conditional Formatting tool > Highlight Cells Rules option > choose Duplicate Values… option.
- As a result, the Duplicate Values window will appear.
- Now, choose the styling in the values with text box and click on the OK button.
Thus, you will see the duplicate student IDs will be filled with light red color with dark red text. And, the result should look like this.
Conclusion
So, in this article, I have shown you 5 suitable methods to remove duplicates and keep the first value in Excel. Read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.
And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!
