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
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 –
- 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:
- Using Range.RemoveDuplicates & Range.SpecialCells(xlCellTypeBlanks).Delete in VBA:
- 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/).
- 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.
- 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.