Search code examples
arraysexcelvbafilterunique

Unique values from 1D-array, without iteration

At risk of being of topic, I decided to share some code, Q&A-style. If the general opinion is such that this would be off-topic I'll be happy to delete if need be.

Background

Can we retrieve all unique values from any 1D-array, or Range object turned into 1D-array, without having to iterate over its elements? As far as I'm concerned the general consensus is that one has to iterate over the different elements, where the best way to do it would either be a dictionary or collection to store unique values in. Here is what I've found works very well for this purpose.

Question

So how would one go about retrieving unique elements from a 1D-array, for example:

Dim arr As Variant: arr = Array("A", "A", "C", "D", "A", "E", "G")

Where the resulting array would be:

{"A", "C", "D", "E", "G"}
Solution

  • Really all code needed are just a few lines:

    Sub test()

Dim arr As Variant: arr = Array("A", "A", "C", "D", "A", "E", "G")
With Application
    uniques = .Index(arr, 1, Filter(.IfError(.Match(.Transpose(.Evaluate("ROW(1:" & UBound(.Match(arr, arr, 0)) & ")")), .Match(arr, arr, 0), 0), "|"), "|", False))
End With

End Sub

    The above will return a 1D-array, returning all unique elements in our original array:

    enter image description here

    Explaination:

    The line that retrieves all these values looks intense, so let's break it into pieces:

    enter image description here

    Application.Match has the ability to work with arrays within it's parameters. So basically we are looking at: .Match({"A","A","C","D","A","E","G"},{"A","A","C","D","A","E","G"},0). The returned array would then be: {1,1,3,4,1,6,7}, and what this really is are the first positions where each value is found at. This result will be the basis on where we build on further.

    enter image description here

    We can see a third .Match in our code which we need to basically tell the following: .Match({1,2,3,4,5,6,7},{1,1,3,4,1,6,7},0). The first parameter is what is retrieved by the above higlighted code.

    Where .Evaluate("ROW(1:" & UBound(.Match(arr, arr, 0)) & ")") will return an array of values from 1-7, the Application.Transpose will return it such that it's a 1D-array.

    enter image description here

    The last step will return an array holding errors, however the code won't break since we are using Application instead of WorksheetFunction. The resulting array will look like {1,Error 2042,3,4,Error 2042,6,7}. Now the whole point is to get rid of the Error values.

    The way to do so is through Application.IfError, which will evaluate the array and change all error values into a give string value. In our case I used the pipe symbol. It's up to the user to decide on a symbol unique enough it won't appear in any of the elements in the original array. So after evaluation. Our current array will look like: {1,|,3,4,|,6,7}.

    enter image description here

    Now we retrieved an array with pipe symbols we would want them out! A quick way to do so is with Filter function. Filter returns an array with or without the elements that fit our criteria (depending on the TRUE or FALSE in it's third paramter).

    So basically we want to return an array like so: Filter(<array>, "|", False). The resulting 1D-array now looks like: {1,3,4,6,7}.

    enter image description here

    We kind of have it at this point. We just need to slice out the correct values from our original array. To do so we can use Application.Index. We just want to tell .Index which rows we are interested in. And to do so we can load our previously found 1D-array. So the code will look like: .Index(arr1, <array>, 1) which will result in a 1D-array: {"A","C","D","E","G"}

    Conclusion:

    There you have it. One line (with more than just a single operation) to retrieve a 1D-array of unique values from another 1D-array without iteration. This code is ready to be used on any 1D-array declared with arr.

    Usefull? I'm not 100% sure, but I finally reached what I was trying in my project. The resulting array can be used immediately in whichever task you need to use unique values in.

    Comparison: Dictionary vs Application.Methods:

    Doing a comparison on random items in the Range(A1:A50000), the performance really takes a hit. Hereby a time-comparison between the iterative Dictionary against the non-iterative Application.Methods approach in 1000 items steps. Below the result of a 1000 items and each 10000 items mark (in seconds):

    | Items     | Dictionary    | Methods       |
|-------    |------------   |-------------  |
| 1000      | 0,02          | 0,03          |
| 10000     | 0             | 0,88          |
| 20000     | 0,02          | 3,31          |
| 30000     | 0,02          | 7,3           |
| 40000     | 0,02          | 12,84         |
| 50000     | 0,03          | 20,2          |

    The Dictionary approach used:

    Sub Test()

Dim arr As Variant: arr = Application.Transpose(Range("A1:A50000"))
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

Dim x As Long

For x = LBound(arr) To UBound(arr)
    dict(arr(x)) = 1
Next x

Dim uniques As Variant: uniques = dict.Keys

End Sub

    Conclusion: Up to a 1000 items this method would be about equal in processing time compared to a more common Dictionary practice. On anything larger, iteration (through memory) will always beat the methods approach!

    I'm sure processing time would be more limited with the new Dynamic Array functions as shown by @ScottCraner.