Retrieving unique values from a range or array …

… 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:

Another site with more detailed information, nicely presented, is:

This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

20 Responses to Retrieving unique values from a range or array …

  1. Jon says:

    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.


  2. dougaj4 says:

    Jon – checking the Excel Blackbelts thread, the original code actually came from StackOverlow:

    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!


  3. Jon says:

    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.


  4. lhm says:

    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…


  5. Luis Fernando Ortiz M. says:

    Amazing!!!, I use this with another functions like small and i can sort and extract uniques in two simple steps.


  6. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

  7. metroxx says:

    Is it possible to make it with sorting?


  8. Pingback: Counting unique values from a range or array | Newton Excel Bach, not (just) an Excel Blog

  9. Pingback: 5 Year Report | Newton Excel Bach, not (just) an Excel Blog

  10. Kirk Duplessis says:

    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.


    • dougaj4 says:

      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.


  11. Pingback: VBA evaluation of defined name (involving INDEX)

  12. 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?


  13. snb says:

    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)
    F_snb = Application.Transpose(.keys)
    End With
    End Function

    in the worksheet:


  14. snb says:

    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)

    F_snb = Application.Transpose(.keys)
    End With
    End Function

    in the worksheet:


    • dougaj4 says:

      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?


  15. snb says:

    It is standard Dictionary behaviour: see
    Nothing has been added to the dictionary but a new key has been made.


  16. Meia Lua says:

    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?



    • dougaj4 says:

      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:
      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.


Leave a Reply

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

You are commenting using your 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