How to Remove Duplicates and Keep the First Value in Excel (5 Methods)

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.

 

Data Set to Remove Duplicates in Excel

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.

Run Remove Duplicates Tool in Excel Toolbar to Remove Duplicates and Keep the First Value

 

Step 2:

Click on Remove Duplicates.

Put a check on all names of the columns you want to erase duplicates from.

Run Remove Duplicates Tool in Excel Toolbar to Remove Duplicates and Keep the First Value

 

Step 3:

Then click OK.

You will get the duplicates removed automatically from your data set.

Run Remove Duplicates Tool in Excel Toolbar to Remove Duplicates and Keep the First Value

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.

Use Advanced Filter of Excel to Remove Duplicates and Keep the First Value

Step 2:

Click on Advanced.

In the Advanced Filter dialogue box, put a check on Unique records only.

Use Advanced Filter of Excel to Remove Duplicates and Keep the First Value

Step 3:

Then click OK.

You will get the duplicate rows removed automatically from your data set.

Use Advanced Filter of Excel to Remove Duplicates and Keep the First Value

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)

Use the UNIQUE Function of Excel to Remove Duplicates and Keep the First Value

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.

Use Power Query to Remove Duplicates and Keep the First Entry

Step 2:

Click on From Table / Range.

In the Create Table dialogue box, Put a check on My table has headers.

Use Power Query to Remove Duplicates and Keep the First Entry

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.

Use Power Query to Remove Duplicates and Keep the First Entry

Step 4:

The duplicate rows will be removed keeping the first rows.

Use Power Query to Remove Duplicates and Keep the First Entry


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.

Run VBA Code to Remove Duplicates and Keep the First Value

Step 2:

➤ Come back to your worksheet.

➤ Select your data set and run this macro.

Run VBA Code to Remove Duplicates and Keep the First Value

➤ This time it will remove the rows only if both the name and the student ID are the same.

Run VBA Code to Remove Duplicates and Keep the First Value

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.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo