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:
Explaination:
The line that retrieves all these values looks intense, so let's break it into pieces:
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.
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.
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}.
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}.
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.
