Create function Unique(array) extracting unique values from an array
To echo other commenters, I use the UNIQUE() function in Google sheets ALL the time. It's fast, efficient, and how Google handles array formulas is brilliant and beautiful as a side note.
In Excel currently, the easiest way I've seen is to copy and paste a range and click Remove Duplicates, but Google's UNIQUE() function updates in real time as the list in the source range changes. That's a huge advantage over having to re-copy and re-remove duplicates each time.
Neale Blackwood commented
This could also make creating formula-based reports easier as entries are automatically added to the list as they appear in the data.
Maybe have options to sort the list =UNIQUE(Range,Sort,Ascending/Descending)
Neale Blackwood commented
=UNIQUE(Range) array entered within a range like the TRANSPOSE function. I'd prefer it wasn't array entered but it may be a requirement of the coding
Extracting unique entries is a common data task. This would make it dynamic and simple.
Currently you have to use a complicated array formula or a UDF to achieve this dynamically.
This function would make creating drop lists from existing data so much easier and more dynamic.
Charley Kyd commented
We need two functions that return arrays: UNIQUE(reference or array) and SORT(reference or array)
Together they could return a unique, sorted lists from a Table column for use with dropdown list boxes.
Yes, we can do this with complicated array formulas. But those formulas calculate VERY slowly with Tables of hundreds of thousands of rows. On the other hand, UNIQUE() and SORT() worksheet functions could be optimized to return their data quickly.
It's included in Google Sheets and so simple and useful ! (no need for complicated array formulas).
Yes please! I would especially find this useful when using data validation. If I want a drop down with values from a range used elsewhere in the table I could use this formula to filter out duplicate entries, rather than having to first copy the range elsewhere and filtering out the duplicate values and using THAT range as my conditional list.
May as well include an ordering option for the final array while we're at it. eg. UNIQUE(array, order option)
Order options: A-Z, Z-A, smallest-largest, etc.
Andreas Killer commented
Arr = UniqueArray(Array(1, 2, 3, 1, 2, 3))
Debug.Print Join(Arr, ", ")
Function UniqueArray(ByVal Arr, _
Optional ByVal Compare As VbCompareMethod = vbBinaryCompare) As Variant
Dim Dict As Object 'Scripting.Dictionary
Set Dict = CreateObject("Scripting.Dictionary")
Dict.CompareMode = Compare
For Each Item In Arr
If Not Dict.Exists(Item) Then Dict.Add Item, Item
UniqueArray = Dict.Items