Removing Duplicates in Excel 2003 or earlier

Filtering out the distinct values of a Column/Variable/Range is a very common need when working with Data. Prior to Excel 2007, there was no in-built functionality in Excel that could easily remove duplicates from single/multiple column.

Excel 2007 & Later – The Remove Duplicates in the Data tab does this with the click of a button.

What if you’re using Excel 2003 or earlier? There are a variety of ways how you can achieve this – starting with the advanced filter, moving to more complicated but easy to replicate methods.

The Advanced Filter

Advanced FilterThe Advanced Filter (from the Data Menu) is the in-built mechanism in Excel to filter out the unique cell values of a variable/column/range. Remember to check the “Unique Records only” check box.

Using this, you have two options,

  • Filter in-place: This option leads to filtering of the range in the current location. Loss of data is inevitable, unless you have a copy of it
  • Copy to another location: This is the more useful option, which helps transport your list of unique values to the desired location

Enter the location of the range to be filtered in “List Range”, and the location of destination in “Copy To”. The “Criteria Range” is used to filter out any desired/undesired criteria, in other words, filtering out the condition required. Filtering using a Criteria has many variants to it, this link provides more context (http://www.contextures.com/xladvfilter01.html).

While the Advanced Filter is a great way to filter, if you need to filter more than once, it can get quite painful. Some of these methods below should come to your rescue –

  1. Using Arrays – The following link (http://spreadsheetpage.com/index.php/eee/issue_no_4_april_30_1999/) explains this method in the “Power Formula Technique”. It basically consists of three arrays,  Thelist: This consists of the expanding list of values to be filtered; sArray: This array consists of the locations of the unique values; and finally, TheUniqueList:Code Snippet for Array Filtering
  2. Using Range.RemoveDuplicates & Range.SpecialCells(xlCellTypeBlanks).Delete in VBA: 
  3. Using the Collections Object in VBA: This method is the fastest method as far as comparison in terms of speed goes. Refer to this page for a ready piece of code, that runs like a darling! (http://www.dailydoseofexcel.com/archives/2008/12/11/create-unique-list-from-selected-cells/).
  4. Using the Dictionary Object in VBA: Dictionary is very similar to Collections in being able to store & handle related data as groups. The advantage of Dictionary over Collections being the use of “KEY” property.
  5. Using the 1D Array Options (split, join, filter): This is a very innovative way of executing the filter, and this was something very new to me too. Look it up in the link provided with the Collections Method.
Advertisements

3 thoughts on “Removing Duplicates in Excel 2003 or earlier

  1. Carlos Rondao

    Here is a very easy efficient method:

    In A1:A10 put A,B,A,C,D,B,C,C,A,D (example values)

    In B1 put value 1
    In B2 the formula:
    =IF(ISERROR(MATCH(A2;A$1:A1;FALSE));MAX(B$1:B1)+1;””)

    copy down to B3:B10

    In C1 put value 1
    In C2 the formula = C1+1

    In D1 the formula:
    =INDEX($A$1:$A$10;MATCH(C1;$B$1:$B$10;FALSE))

    Copy to D2

    and copy C2:D2 down…

    Now you have in column D all unique values from column A

    Reply
  2. Max

    Nice blog right here! Also your site a lot up very fast!
    What web host are you the usage of? Can I am getting your associate hyperlink to your host?
    I desire my website loaded up as fast as yours lol

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s