… using the Scripting Dictionary object.
in a previous post I looked at using the dictionary object to count connected elements in a finite element model. This post looks at a more general (and simpler) usage to return unique values from a worksheet range or VBA array. It also looks at some more features of the dictionary object. It was prompted by a discussion at the LinkedIn (private) Excel Blackbelts forum. A spreadsheet with the examples shown below, and full open source code, may be downloaded from: GetUnique.xlsb
The Unique() User Defined Function (UDF) shown below returns a single column array containing all the unique values (numbers or text) in DRange, which is defined as a Variant so that it will accept a spreadsheet range or an array from another VBA routine.
Function Unique(DRange As Variant) As Variant
Dim Dict As Object
Dim i As Long, j As Long, NumRows As Long, NumCols As Long
'Convert range to array and count rows and columns
If TypeName(DRange) = "Range" Then DRange = DRange.Value2
NumRows = UBound(DRange)
NumCols = UBound(DRange, 2)
'put unique data elements in a dictionay
Set Dict = CreateObject("Scripting.Dictionary")
For i = 1 To NumCols
For j = 1 To NumRows
Dict(DRange(j, i)) = 1
Next j
Next i
'Dict.Keys() is a Variant array of the unique values in DRange
'which can be written directly to the spreadsheet
'but transpose to a column array first
Unique = WorksheetFunction.Transpose(Dict.keys)
End Function
The line that does all the work:
Dict(DRange(j, i)) = 1
simply creates a new dictionary key if the contents of DRange(j,1) do not yet exist in the dictionary object, or writes over the old key with an identical new one if it does exist. The result is an array with one copy of each unique value from DRange.
Note that in this function the Dictionary Object is created with the line:
Set Dict = CreateObject(“Scripting.Dictionary”)
Dict having been dimensioned as an Object.
Using this method allows the Dictionary object to be created and accessed without having a VBA reference to the Scripting Library (at least in Excel 2010), but it does restrict some of the functionality of the object. I haven’t tested this function in earlier versions, but if you find it doesn’t work going into the VB Editor and enabling the reference to the Microsoft Scripting Runtime should fix it.
I have written a second UDF, UniqueR(), which requires a reference to the Scripting Library in VBA, but allows greater control of the dictionary object. Details of adding a reference to the Scripting Library are given in the previous post.
The UniqueR function has two optional parameters:
=Unique(Datarange) or UniqueR(DataRange, Cmode, Out)
CMode Options:
0 = BinaryCompare
1 = TextCompare
2 = DatabaseCompare
Out Options:
0 = Array of unique items
1 = Number of unique items followed by array
>1 = Number of unique items only
Examples of the use of the two functions (including instructions for entering an array formula) are shown in the screenshots below:
UDF options and instructions for entering array functions
Unique Function and Uniquer Function with TextCompare option
Uniquer Function with Out option = 2 and an input range of 10,000 rows
Microsoft documentation of the Dictionary object is fragmented, and mostly related to VB rather than VBA, but a reasonable introduction is given at: http://support.microsoft.com/kb/246067
Another site with more detailed information, nicely presented, is: http://www.stealthbot.net/wiki/Scripting.Dictionary.
Newton Excel Bach, not (just) an Excel Blog 
On StackOverflow something similar to this was done. I always thought that built in Excel functions would work faster, even in VBA, not the case using the dictionary function is amazingly fast! Your rendition is much more clean cut than what was done on StackOverflow.
LikeLike
Jon – checking the Excel Blackbelts thread, the original code actually came from StackOverlow:
http://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array
I’m glad you like my version. I’m sure the Dictionary Object has great potential to do things simpler and faster, we just need to think of them!
LikeLike
I use them for classes too, since they are supposedly faster than collections, unless you need to provide order (like inserting) than they seem to be the way to go.
LikeLike
I wasn’t aware that dictionaries were significantly faster than collections, but it seems so! Another way of getting uniques from an array is via a consolidation PivotTable with ‘Value’ as row field.
In Excel 2010 this can be done by pressing sequentially Alt-D-P…
Step1: Click Multiple Consolidation Ranges
Step2a: I will create the page fields
Step2b: Select the range a22:c33 [including blank row and col headings]
Step3: Existing Worksheet: M22
In the pivottable list, uncheck row and column and drag Value to the row label position
This can be recorded and has the advantage that you don’t need to specify the length of the output range, but it still needs to be refreshed after the data changes. I’ve been hoping for an autorefresh option for PTs, maybe it’ll come in Excel 15…
LikeLike
Amazing!!!, I use this with another functions like small and i can sort and extract uniques in two simple steps.
LikeLike
Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog
Hello,
Is it possible to make it with sorting?
LikeLike
You could use the built-in small() function, as suggested above, or you could use my sort function: https://newtonexcelbach.wordpress.com/2009/03/23/a-sort-function/
LikeLike
Pingback: Counting unique values from a range or array | Newton Excel Bach, not (just) an Excel Blog
Pingback: 5 Year Report | Newton Excel Bach, not (just) an Excel Blog
Hi Doug,
I’m having trouble getting this to work, but I desperately want it to.
I’ve got a sheet with roughly 100 columns by 10000 rows, and I want to create a list of the unique items in that array.
So, I pasted the above VBA into Excel, and presto, I have the Unique function; so far so good.
Unfortunately, when I enter (for example) =unique(a1:b5) followed by CTRL+SHIFT+ENTER into C1 and drag down, I simply get the values in A1:A5, some of which are repeats.
I suspect that I’ve missed a setting somewhere; any help would be much appreciated.
LikeLike
Kirk – you are nearly there. You can either select the whole output range first, and enter the function with Ctrl-Shift-Enter, or enter the function in the top left cell, then select the whole output range (including the cell with the function), press F2 to enter edit-mode, then press Ctrl-Shift-Enter.
LikeLike
Pingback: VBA evaluation of defined name (involving INDEX)
I know this web page presents quality based articles or reviews and extra stuff, is there
any other site which gives such stuff in quality?
LikeLike
It isn’t even necessary to ‘fill’ the dictionary.
this code suffices:
Function F_snb(c00)
With CreateObject(“scripting.dictionary”)
For Each it In c00
x = .Item(it)
Next
F_snb = Application.Transpose(.keys)
End With
End Function
in the worksheet:
‘=F_snb(A1:K20)
LikeLike
I get identical results to your UniqueR function using the ‘late binding’ to the scripting Library
NB. In my previous post x=.Item(it) must be written as x=.Item(it.value)
Function F_snb(c00, y)
With CreateObject(“scripting.dictionary”)
.comparemode = y
For Each it In c00
x = .Item(it.Value)
Next
F_snb = Application.Transpose(.keys)
End With
End Function
in the worksheet:
“=F_snb(B23:C33,1)”
LikeLike
Thanks for the suggestions snb, I’ll give them a go.
I’m not very familiar with using dictionaries, but it does seem strange to me that if you create an empty dictionary, then assign a variable a value that is not in the dictionary, that value is magically added to the dictionary.
Is that a VBA thing, or standard dictionary behaviour?
LikeLike
It is standard Dictionary behaviour: see http://www.snb-vba.eu/VBA_Dictionary_en.html.
Nothing has been added to the dictionary but a new key has been made.
LikeLike
This is the fastest function I know of to filter unique values…A must! Thanks!
However I have been trying to use the formula on a large array (15000 rows) containing long texts. The function does not work past the 8000 row: OK for up to 8000 #VALUE! when extending the array to 9000.
Is there any limitations as to the number of characters per cell or as to the range containing the values to be filtered?
Thanks!
LikeLike
I can’t help I’m afraid. I don’t know what the limits are, and a quick search was very unhelpful.
There was a reference to at least one million entries here:
http://stackoverflow.com/questions/15342751/what-is-the-maximum-size-of-scripting-dictionary
but that would have been small objects (probably doubles), not long strings.
One thing that may be causing the problem is the worksheetfunction.transpose. You might try replacing that with a loop and see if that solves the problem.
If you do find a solution (or a definite upper limit) I’d be very interested to hear it.
LikeLike