Removing Duplicate Rows / Find Unique Values in Excel

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

1 comment