Getting unique values in Excel by using formulas only

Solution 1:

Ok, I have two ideas for you. Hopefully one of them will get you where you need to go. Note that the first one ignores the request to do this as a formula since that solution is not pretty. I figured I make sure the easy way really wouldn't work for you ;^).

Use the Advanced Filter command

1. Select the list (or put your selection anywhere inside the list and click ok if the dialog comes up complaining that Excel does not know if your list contains headers or not)
2. Choose Data/Advanced Filter
3. Choose either "Filter the list, in-place" or "Copy to another location"
4. Click "Unique records only"
5. Click ok
6. You are done. A unique list is created either in place or at a new location. Note that you can record this action to create a one line VBA script to do this which could then possible be generalized to work in other situations for you (e.g. without the manual steps listed above).

Using Formulas (note that I'm building on Locksfree solution to end up with a list with no holes)

This solution will work with the following caveats:

Solution 2:

This is an oldie, and there are a few solutions out there, but I came up with a shorter and simpler formula than any other I encountered, and it might be useful to anyone passing by.

I have named the colors list Colors (A2:A7), and the array formula put in cell C2 is this (fixed):

=IFERROR(INDEX(Colors,MATCH(SUM(COUNTIF(C$1:C1,Colors)),COUNTIF(Colors,"<"&Colors),0)),"")

Use Ctrl+Shift+Enter to enter the formula in C2, and copy C2 down to C3:C7.

Explanation with sample data {"red"; "blue"; "red"; "green"; "blue"; "black"}:

1. COUNTIF(Colors,"<"&Colors) returns an array (#1) with the count of values that are smaller then each item in the data {4;1;4;3;1;0} (black=0 items smaller, blue=1 item, red=4 items). This can be translated to a sort value for each item.
2. COUNTIF(C$1:C...,Colors) returns an array (#2) with 1 for each data item that is already in the sorted result. In C2 it returns {0;0;0;0;0;0} and in C3 {0;0;0;0;0;1} because "black" is first in the sort and last in the data. In C4 {0;1;0;0;1;1} it indicates "black" and all the occurrences of "blue" are already present.
3. The SUM returns the k-th sort value, by counting all the smaller values occurrences that are already present (sum of array #2).
4. MATCH finds the first index of the k-th sort value (index in array #1).
5. The IFERROR is only to hide the #N/A error in the bottom cells, when the sorted unique list is complete.

To know how many unique items you have you can use this regular formula:

=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))

Solution 3:

Solution

I created a function in VBA for you, so you can do this now in an easy way.
Create a VBA code module (macro) as you can see in this tutorial.

1. Press Alt+F11
2. Click to Module in Insert.
3. Paste code.
4. If Excel says that your file format is not macro friendly than save it as Excel Macro-Enabled in Save As.

Source code

Function listUnique(rng As Range) As Variant
    Dim row As Range
    Dim elements() As String
    Dim elementSize As Integer
    Dim newElement As Boolean
    Dim i As Integer
    Dim distance As Integer
    Dim result As String

    elementSize = 0
    newElement = True

    For Each row In rng.Rows
        If row.Value <> "" Then
            newElement = True
            For i = 1 To elementSize Step 1
                If elements(i - 1) = row.Value Then
                    newElement = False
                End If
            Next i
            If newElement Then
                elementSize = elementSize + 1
                ReDim Preserve elements(elementSize - 1)
                elements(elementSize - 1) = row.Value
            End If
        End If
    Next

    distance = Range(Application.Caller.Address).row - rng.row

    If distance < elementSize Then
        result = elements(distance)
        listUnique = result
    Else
        listUnique = ""
    End If
End Function

Usage

Just enter =listUnique(range) to a cell. The only parameter is range that is an ordinary Excel range. For example: A$1:A$28 or H$8:H$30.

Conditions

• The range must be a column.
• The first cell where you call the function must be in the same row where the range starts.

Example

Regular case

1. Enter data and call function.
   
2. Grow it.
   
3. Voilà.
   

Empty cell case

It works in columns that have empty cells in them. Also the function outputs nothing (not errors) if you overwind the cells (calling the function) into places where should be no output, as I did it in the previous example's "2. Grow it" part.

Solution 4:

A roundabout way is to load your Excel spreadsheet into a Google spreadsheet, use Google's UNIQUE(range) function - which does exactly what you want - and then save the Google spreadsheet back to Excel format.

I admit this isn't a viable solution for Excel users, but this approach is useful for anyone who wants the functionality and is able to use a Google spreadsheet.

Solution 5:

noticed its a very old question but people seem still having trouble using a formula for extracting unique items. here's a solution that returns the values them selfs.

Lets say you have "red", "blue", "red", "green", "blue", "black" in column A2:A7

then put this in B2 as an array formula and copy down =IFERROR(INDEX(A$2:A$7;SMALL(IF(FREQUENCY(MATCH(A$2:A$7;A$2:A$7;0);ROW(INDIRECT("1:"&COUNTA(A$2:A$7))));ROW(INDIRECT("1:"&COUNTA(A$2:A$7)));"");ROW(A1)));"")

then it should look something like this;