logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Removing Duplicate Rows / Find Unique Values in Excel

February 19, 2015 1 comment Article Easy, Excel
[Excel 2007, 2010, 2013]

Anyone who’s used Excel in earnest for any length of time – ie 2003 and earlier – probably remembers having to create a VBA add-in to tackle the removal of duplicate values – or sometimes a COUNTIF() function would suffice for smaller jobs.

I always used the superb ‘The Duplicate Master’ add-in

(Which can still be found here – though the availability of the link seems to come & go)

Whilst such add-ins still have a place, offering rich features, for basic day-to-day removal of duplicates, Excel now includes this functionality built-in – and has done since Office 2007.

It’s this built-in functionality that we’ll cover in this post.

I’ve made up some data about in what year I started following certain people on Twitter. Some names appear twice – perhaps I stopped following them, and then started again!

I want a unique list of names of who I follow. Take a copy of the data you want to analyse and past it out of the way, and then select the data you just copied.

On the DATA tab, in the ‘Data Tools’ group, select ‘Remove Duplicates’

In most cases, you can simply press ‘OK’ on the dialog box that appears, accepting the default values.

And the result is a list of the unique names

However, this doesn’t give me a list of everyone I follow, as both my Stephens have been counted as 1.

This time, highlight the whole data range (optionally copy and paste again as we did before).

Select Remove Duplicates and you’ll see that it recognises that we have several columns of data.

If in your data you want to check for where the entire row is duplicated go ahead and accept the default values.

In this example though, that won’t have any effect, as they’re already unique rows.

Instead, I’m going to tell it to ignore year as a value and just compare First Name & Surname

This time, you’ll see it removes one value – my duplicated ‘Bob Dylan’ entry.

 

And there isn’t really any more to it than that.

David

Related

Tags: Excel, Formatting, Office, Productivity, Shortcuts

1 comment

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Showing a Picture Based on the Value of another Cell in Excel
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Showing Data Bars as a 'Proportion to Total' in Excel
Format or Remove Borders from a Slicer or Timeline in Excel
Populate a Dropdown Based on the Value of Another Dropdown in PowerApps
Using the same Named Range in multiple worksheets, but with different values, in Excel

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2021 - Theme by ThemeinProgress