How can we improve Excel for Windows (Desktop Application)?

Create function Unique(array) extracting unique values from an array

27 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Jamil Mohammad shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    7 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Dtork commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        =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  ·   ·  Flag as inappropriate

        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.

      • Daniel commented  ·   ·  Flag as inappropriate

        It's included in Google Sheets and so simple and useful ! (no need for complicated array formulas).

      • Graham commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        Sub Example_UniqueArray()
        Dim Arr
        Arr = UniqueArray(Array(1, 2, 3, 1, 2, 3))
        Debug.Print Join(Arr, ", ")
        End Sub

        Function UniqueArray(ByVal Arr, _
        Optional ByVal Compare As VbCompareMethod = vbBinaryCompare) As Variant
        Dim Dict As Object 'Scripting.Dictionary
        Dim Item
        Set Dict = CreateObject("Scripting.Dictionary")
        Dict.CompareMode = Compare
        For Each Item In Arr
        If Not Dict.Exists(Item) Then Dict.Add Item, Item
        Next
        UniqueArray = Dict.Items
        End Function

      Feedback and Knowledge Base