Rifat Hassan
One of the most important and widely used tasks in Excel is to remove duplicate values from a data set. Today I’ll show you how to remove duplicates from your data set and keep the first value at the same time.
Download Practice Workbook
5 Methods to Remove Duplicates and 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.
1. Run Remove Duplicates Feature from Excel Toolbar
Step 1:
➤ Select the whole data set.
➤ Go to Data > Remove Duplicates tool in Excel Toolbar under the section Data Tools.
Step 2:
➤ Click on Remove Duplicates.
➤ Put a check on all names of the columns you want to erase duplicates from.
Step 3:
➤ Then click OK.
➤ You will get the duplicates removed automatically from your data set.
Read more: How to Remove Duplicates Based on Criteria in Excel
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.
Step 1:
➤ Select the whole data set.
➤ Go to Data > Advanced tool in Excel Toolbar under the section Sort & Filter.
Step 2:
➤ Click on Advanced.
➤ In the Advanced Filter dialogue box, put a check on Unique records only.
Step 3:
➤ Then click OK.
➤ You will get the duplicate rows removed automatically from your data set.
Read more: How to Remove Duplicate Rows in Excel
3. Insert the 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.
Select a new column and enter this formula:
=UNIQUE(B4:E14,FALSE,FALSE)
It will delete the rows with duplicate values while keeping the first one, and create a fresh copy of the data set in a 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.
Similar Readings:
4. Use Power Query to Remove Duplicates and Keep the First Entry
Step 1:
➤ Select the whole data set.
➤ Go to Data > From Table / Range tool in Excel Toolbar under the section Get & Transform Data.
Step 2:
➤ Click on From Table / Range.
➤ In the Create Table dialogue box, Put a check on My table has headers.
Step 3:
➤ Then click OK.
➤ The Power Query Editor will open with your data set.
➤ From the Remove Rows option under the Home tab, click on Remove Duplicates.
Step 4:
➤ The duplicate rows will be removed keeping the first rows.
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.
Step 1:
➤ Open a new VBA window and insert another new module (Click here to see how to open a new VBA module in Excel)
➤ Insert this code in the module:
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.
Step 2:
➤ Come back to your worksheet.
➤ Select your data set and run this macro.
➤ This time it will remove the rows only if both the name and the student ID are the same.
Note: Here it has not removed Frank Orwell because the IDs of the two students are different, that is, they are two different students.
Conclusion
Using these methods, you can remove the duplicate values while keeping the first one from your data set in Excel. Do you have any questions? Feel free to ask us.
