“Filtering out the distinct values of a Column/Variable/Range” is one of the most sought after functions in Analytical Reporting. Yet, it fails to figure upfront in the “Home” section of Excel’s Ribbon! Is it something that you wouldn’t need often in Excel? If no, how easy is it to use the currently available options in Excel? Are there alternatives? Please read further…
Primarily, the need for this function is for multiple key merges. In other tools, you generally have in-built mechanisms to consider only the distinct values of the primary key, and merge two datasets with multiple keys. Excel hardly has anything of this mechanism, and to be more precise, merging in excel is limited to “single-key merges”. Merge not being the topic in question, I’ll let that pass this time.
The “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. Excel 2007 has a “Remove Duplicates” key, which is a definitely useful, but as far as 2003 is concerned, it’s still short of user friendliness.
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, and I am not going to delve into it. Nevertheless, here’s a reference for the same. This link (http://www.contextures.com/xladvfilter01.html) provide exhaustive information on Advanced Filter, and also on Criteria Range variants.
If there is a need to filter the distinct items quite a lot of times, I bet anyone would get frustrated following the above process and willl proceed to look for alternatives. That is what happened with me too, and I hit a couple of gold mines! Let me put down the various methods that can be adopted to do this filtering I’ve been talking about. Honestly, it still intrigues me to find a new method everytime!
- Using Arrays - This method still evades me. 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: This array consists of the sorted list of values. I’m not very sure how we can convert this array back into the normal Excel ranges. As I said, the array concepts are making it a little tricky, so I’m working on it to get to know it’s full potential. Here’s the part of it I am talking about.
- Using Range.RemoveDuplicates & Range.SpecialCells(xlCellTypeBlanks).Delete in VBA: Using this is really quick and simple, though customizing this to your needs may require some attention.
- 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 readymade piece of code, that runs like a darling! (http://www.dailydoseofexcel.com/archives/2008/12/11/create-unique-list-from-selected-cells/). Again, I’m not very versatile with Collections, so I can’t explain much. But it’s pretty slick. The page also has various other techniques listed, some of which I’ll jot down below.
- 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. For more comprehensive understanding, I would recommend visiting Pearson’s site (http://www.cpearson.com/excel/CollectionsAndDictionaries.htm).
- 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.
And believe me, there are other unexplored methods too. Phew! That’s some exhaustive work!
This is only interesting as it gets deeper, but from the surface, I would believe a small code on the Collections or the RemoveDuplicates should be more than helpful & quick. So, if you could make yourself an Add-In, it might be useful too; only if you’re frequently going to use something like this.
