Feedback by UserVoice

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

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

40 votes
Sign in
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

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


Sign in
Password icon
Signed in as (Sign out)
  • AHarris commented  ·   ·  Flag as inappropriate

    This would be good.

    I currently:
    Create a pivot to list the unique values (then copy/paste it to use as a lookup) if a static list will do.
    Create a UDF for when I need dynamic uniques (slow if used in a lot of formulas).
    Use convoluted formulas when I'm confident no other user needs to understand my mess.
    Build up logic over several columns of data when I need it for many rows and others need to use this spreadsheet too - this logic may depend on sorted data which makes it fragile and sometimes impractical - leading to more complex formulas to handle non-sorted data.

    An optimised function would be great - I would use it in probably 10% of spreadsheets I work with. Probably 35% of the non-trivial spreadsheets.

    Possible uses/variants:
    - Counting unique values e.g. via =count(unique(range)).
    - Also sum, average, median, stddev, etc.. (Yes I have wanted to get the median of the unique values rather than of the whole population in the past)
    - Getting the Kth largest or smallest unique value e.g. via =large(unique(range), 3).
    - Controlling data validation, list constraint to the values returned by the unique function.
    - Consolidating data easily via =unique(range1, range2, range3).
    - Possible array entry over a range to output the whole (or part) of the unique list ={unique(range)}.
    - Use as a variant of the choose function =unique( index_num, range) where index_num can be a cell reference.
    - count unique range buckets =unique(range, roundto) where rounto behaves like in the FLOOR function, e.g. unique(range, 500) would floor the numbers to the nearest 500 and return the resulting unique values list. e.g. return 0, 500,1000 from 101,107,524,600,897,1278 as input.

    These are all things I have used more complex methods to achieve when I have wanted them. While the more complex methods are available (yay Excel) a simple function to return unique values is something I have wanted many times over many years.

  • Kevin Osborn commented  ·   ·  Flag as inappropriate

    Please. The array formulas to find extract unique lists are super complex. In doing so it will need to account for "" values (included or not) and sorting also a necessity.

  • 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
    UniqueArray = Dict.Items
    End Function

Feedback and Knowledge Base