How to Remove Duplicates but Keep the First Value in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Dataset to Remove Duplicates and Keep First Value in Excel


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.

Using Remove Duplicates Tool to Remove Duplicates and Keep First Value in Excel

  • Now, put a check on all names of the columns you want to erase duplicates from.
  • Subsequently, click on the OK button.

Remove Duplicates Window

Thus, you will get the duplicates removed automatically from your data set.

Removed Duplicates and Kept the First Value in Excel

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.

Using Advanced Filter to Remove Duplicates and Keep First Value in Excel

  • 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.

Advanced Filter Dialogue Box

Consequently, you will get the duplicate rows removed automatically from your data set.

Removed Duplicates and Kept First Value in Excel

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.

Using the UNIQUE Function to Remove Duplicates and Keep First Value in Excel

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


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.

Access Get & Transform Data Tool

  • 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.

Create Table Window

  • 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.

Click on Remove Duplicates to Remove Duplicates and Keep First Values in Excel

  • Consequently, the duplicate rows will be removed keeping the first rows.

Removed Duplicates Keeping the First Value in Power Query

  • Now, under the Home tab, click on the Close & Load button.

Access 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.

Removed Duplicates and Kept First Value in Excel

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.

Access the Visual Basic Tool

  • Afterward, the VB Editor will open.
  • Following, click on the Insert tab > choose the Module option.

Insert a Module to Remove Duplicates and Keep First Value in Excel

  • 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.

VBA Code to Remove Duplicates and Keep First Value in Excel

  • Now, a Microsoft Excel dialogue box will appear.
  • Following, click on the No button.

Microsoft Excel Dialogue Box

  • 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.

Save the Excel File as .xlsm File

  • 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.

Access the Macros Tool to Remove Duplicates and Keep First Value in Excel

  • As a result, the Macro window will appear.
  • Following, choose the Remove_Duplicates macro and click on the Run button.

Run the Macro to Remove Duplicates and Keep First Value in Excel

Thus, the duplicates will be removed and the first values will remain. And, the outcome should look like this.

Removed Duplicates and Kept First Value in Excel

Note:

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.

Use Conditional Formatting to Highlight Duplicate Values without Deleting

  • 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.

Duplicate Values Window

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.

Highlighted Duplicates without Deleting Values


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!


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 the 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo