Getting unique values in Excel by using formulas only

Question

Do you know a way in Excel to "calculate" by formula a list of unique values ?

E.g: a vertical range contains values "red", "blue", "red", "green", "blue", "black"
and I want to have as result "red, "blue", "green", "black" + eventually 2 other blank cells.

I already found a way to get a calculated sorted list using SMALL or LARGE combined with INDEX, but I'd like to have this calculated sort as well, WITHOUT USING VBA.

Answer 1

Simple formula solution: Using dynamic array functions (UNIQUE function)

Since fall 2018, the subscription versions of Microsoft Excel (Office 365 / Microsoft 365 app) contain so called dynamic array functions (not yet available in Office 2016/2019 nonsubscription versions).

UNIQUE function

One of those functions is the UNIQUE function that will deliver an array of unique values for the selected range.

Example

In the following example, the input values are in range A1:A6. The UNIQUE function is typed into cell C1.

=UNIQUE(A1:A6)

As you can see, the UNIQUE function will automatically spill over the necessary range of cells in order to show all unique values. This is indicated by the thin, blue frame around C1:C4.

Good to know

As the UNIQUE function automatically spills over the necessary number of rows, you should leave enough space under the C1. If there is not enough space, you will get a #SPILL error.

If you want to reference the results of the UNIQUE function, you can just reference the cell containing the UNIQUE function and add a hash # sign.

=C1#

It is also possible to check unique values in several columns. In this case, the UNIQUE function will deliver all rows where the combination of the cells within the row are unique:

If you wish to show unique columns instead of unique rows, you have to set the [by_col] argument to TRUE (default is FALSE, meaning you will receive unique rows).

You can also show values that appear exactly once by setting the [exactly_once] argument to TRUE:

=UNIQUE(A1:A6;;TRUE)

Answer 2

Try this formula in B2 cell

=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF(B$1:$B1,$A$2:$A$7),0),1),"")

After click F2 and press Ctrl + Shift + Enter

Answer 3

Resorting to a PivotTable might not count as using formulas only but seems more practical that most other suggestions so far:

Answer 4

Optimized VBScript Solution

I used totymedli's code but found it bogging down when using large ranges (as pointed out by others), so I optimized his code a bit. If anyone is interested in getting unique values using VBScript but finds totymedli's code slow when updating, try this:

    Function listUnique(rng As Range) As Variant
        Dim val As String
        Dim elements() As String
        Dim elementSize As Integer
        Dim newElement As Boolean
        Dim i As Integer
        Dim distance As Integer
        Dim allocationChunk As Integer
        Dim uniqueSize As Integer
        Dim r As Long
        Dim lLastRow  As Long

        lLastRow = rng.End(xlDown).row

        elementSize = 1
        unqueSize = 0

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

        If distance <> 0 Then
            If Cells(Range(Application.Caller.Address).row - 1, Range(Application.Caller.Address).Column).Value = "" Then
                listUnique = ""
                Exit Function
            End If
        End If

        For r = 1 To lLastRow
            val = rng.Cells(r)
            If val <> "" Then
                newElement = True
                For i = 1 To elementSize - 1 Step 1
                    If elements(i - 1) = val Then
                        newElement = False
                        Exit For
                    End If
                Next i
                If newElement Then
                    uniqueSize = uniqueSize + 1
                    If uniqueSize >= elementSize Then
                        elementSize = elementSize * 2
                        ReDim Preserve elements(elementSize - 1)
                    End If
                    elements(uniqueSize - 1) = val
                End If
            End If
        Next


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

Answer 5

If one puts all the data in the same columns and uses the following formula Example Formula: =IF(C105=C104,"Duplicate","Not a Duplicate")

Steps

1. Sort the data
2. Add column for the formula
3. Checks if the cell equals the cell above it
4. Then filter Not a Duplicate
5. Optional: Copy the data calculated by the formula column and paste as values only (that way if you start deleting data, you don't start to get errors
6. NOTE/WARNING: This only works if you sort the data first

Example Formula: =IF(C105=C104,"Duplicate","Not a Duplicate")

Answer 6

For a solution that works for values in multiple rows and columns, I found the following formula very useful, from http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/ Oscar at get-digital.help.com even goes through it step-by-step and with a visualized example.

1) Give the range of values the label tbl_text

2) Apply the following array formula with CTRL + SHIFT + ENTER, to cell B13 in this case. Change $B$12:B12 to refer to the cell above the cell you enter this formula into.

    =INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)

3) Copy/drag down until you get N/A's.

Answer 7

Even to get a sorted unique value, it can be done using formula. This is an option you can use:

=INDEX($A$2:$A$18,MATCH(SUM(COUNTIF($A$2:$A$18,C$1:C1)),COUNTIF($A$2:$A$18,"<" &$A$2:$A$18),0))

range data: A2:A18

formula in cell C2

This is an ARRAY FORMULA

Answer 8

I've pasted what I use in my excel file below. This picks up unique values from range L11:L300 and populate them from in column V, V11 onwards. In this case I have this formula in v11 and drag it down to get all the unique values.

=INDEX(L$11:L$300,MATCH(0,COUNTIF(V$10:V10,L$11:L$300),0))

or

=INDEX(L$11:L$300,MATCH(,COUNTIF(V$10:V10,L$11:L$300),))

this is an array formula

Answer 9

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))

Answer 10

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;

Answer 11

I'm surprised this solution hasn't come up yet. I think it's one of the easiest

Give your data a heading and put it into a dynamic named range (i.e. if your data is in col A)

=OFFSET($A$2,0,0,COUNTA($A:$A),1)

And then create a pivot table, making the source your named range.

Simply putting the heading into the rows section and you'll have the unique values, sort any way you like with the inbuilt feature.

Answer 12

Select the column with duplicate values then go to Data Tab, Then Data Tools select remove duplicate select 1) "Continue with the current selection" 2) Click on Remove duplicate.... button 3) Click "Select All" button 4) Click OK

now you get the unique value list.

Answer 13

This only works if the values are in order i.e all the "red" are together and all the "blue" are together etc. assume that your data is in column A starting in A2 - (Don't start from row 1) In the B2 type in 1 In b3 type =if(A2 = A3, B2,B2+1) Drag down the formula until the end of your data All " Red" will be 1 , all "blue" will be 2 all "green" will be 3 etc.

In C2 type in 1, 2 ,3 etc going down the column In D2 = OFFSET($A$1,MATCH(c2,$B$2:$B$x,0),0) - where x is the last cell Drag down, only the unique values will appear. -- put in some error checking

Answer 14

You can also do it this way.

Create the following named ranges:

nList = the list of original values
nRow = ROW(nList)-ROW(OFFSET(nList,0,0,1,1))+1
nUnique = IF(COUNTIF(OFFSET(nList,nRow,0),nList)=0,COUNTIF(nList, "<"&nList),"")

With these 3 named ranges you can generate the ordered list of unique values with the formula below. It will be sorted in ascending order.

IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-?),nUnique,0)),"")

You will need to substitute the row number of the cell just above the first element of your unique ordered list for the '?' character.

eg. If your unique ordered list begins in cell B5 then the formula will be:

IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-4),nUnique,0)),"")

Answer 15

Drew Sherman's solution is very good, but the list must be contiguous (he suggests manually sorting, and that is not acceptable for me). Guitarthrower's solution is kinda slow if the number of items is large and don't respects the order of the original list: it outputs a sorted list regardless.

I wanted the original order of the items (that were sorted by the date in another column), and additionally I wanted to exclude an item from the final list not only if it was duplicated, but also for a variety of other reasons.

My solution is an improvement on Drew Sherman's solution. Likewise, this solution uses 2 columns for intermediate calculations:

Column A:

The list with duplicates and maybe blanks that you want to filter. I will position it in the A11:A1100 interval as an example, because I had trouble moving the Drew Sherman's solution to situations where it didn't start in the first line.

Column B:

This formula will output 0 if the value in this line is valid (contains a non-duplicated value). Note that you can add any other exclusion conditions that you want in the first IF, or as yet another outer IF.

=IF(ISBLANK(A11);1;IF(COUNTIF($A$11:A11;A11)=1;0;COUNTIF($A11:A$1100;A11)))

Use smart copy to populate the column.

Column C:

In the first line we will find the first valid line:

=MATCH(0;B11:B1100;0)

From that position, we search for the next valid value with the following formula:

=C11+MATCH(0;OFFSET($B$11:$B$1100;C11;0);0)

Put it in the second line and use smart copy to fill the rest of the column. This formula will output #N/D error when there is no more unique itens to point. We will take advantage of this in the next column.

Column D:

Now we just have to get the values pointed by column C:

=IFERROR(INDEX($A$11:$A$1100; C11); "")

Use smart copy to populate the column. This is the output unique list.

Answer 16

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.

Answer 17

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.

Answer 18

Assuming Column A contains the values you want to find single unique instance of, and has a Heading row I used the following formula. If you wanted it to scale with an unpredictable number of rows, you could replace A772 (where my data ended) with =ADDRESS(COUNTA(A:A),1).

=IF(COUNTIF(A5:$A$772,A5)=1,A5,"")

This will display the unique value at the LAST instance of each value in the column and doesn't assume any sorting. It takes advantage of the lack of absolutes to essentially have a decreasing "sliding window" of data to count. When the countif in the reduced window is equal to 1, then that row is the last instance of that value in the column.

Answer 19

I ran into the same problem recently and finally figured it out.

Using your list, here is a paste from my Excel with the formula.

I recommend writing the formula somewhere in the middle of the list, like, for example, in cell C6 of my example and then copying it and pasting it up and down your column, the formula should adjust automatically without you needing to retype it.

The only cell that has a uniquely different formula is in the first row.

Using your list ("red", "blue", "red", "green", "blue", "black"); here is the result: (I don't have a high enough level to post an image so hope this txt version makes sense)

• [Column A: Original List]
• [Column B: Unique List Result]

• [Column C: Unique List Formula]

  1. red, red, =A3
  2. blue, blue, =IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")
  3. red, , =IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")
  4. green, green, =IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")
  5. blue, , =IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")
  6. black, black, =IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")

Answer 20

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:

The list must be sorted (ascending or descending does not matter). Actually that's quite accurate as the requirement is really that all like items must be contiguous but sorting is the easiest way to reach that state.

Three new columns are required (two new columns for calculations and one new column for the new list). The second and third columns could be combined but I'll leave that as an exercise to the reader.

Here is the summary of the solution:

1. For each item in the list, calculate the number of duplicates above it.
2. For each place in the unique list, calculate the index of the next unique item.
3. Finally, use the indexes to create a new list with only unique items.

And here is a step by step example:

1. Open a new spreadsheet
2. In a1:a6 enter the example given in the original question ("red", "blue", "red", "green", "blue", "black")
3. Sort the list: put the selection in the list and choose the sort command.
4. In column B, calculate the duplicates:
   1. In B1, enter "=IF(COUNTIF($A$1:A1,A1) = 1,0,COUNTIF(A1:$A$6,A1))". Note that the "$" in the cell references are very important as it will make the next step (populating the rest of the column) much easier. The "$" indicates an absolute reference so that when the cell content is copy/pasted the reference will not update (as opposed to a relative reference which will update).
   2. Use smart copy to populate the rest of column B: Select B1. Move your mouse over the black square in the lower right hand corner of the selection. Click and drag down to the bottom of the list (B6). When you release, the formula will be copied into B2:B6 with the relative references updated.
   3. The value of B1:B6 should now be "0,0,1,0,0,1". Notice that the "1" entries indicate duplicates.
5. In Column C, create an index of unique items:
   1. In C1, enter "=Row()". You really just want C1 = 1 but using Row() means this solution will work even if the list does not start in row 1.
   2. In C2, enter "=IF(C1+1<=ROW($B$6), C1+1+INDEX($B$1:$B$6,C1+1),C1+1)". The "if" is being used to stop a #REF from being produced when the index reaches the end of the list.
   3. Use smart copy to populate C3:C6.
   4. The value of C1:C6 should be "1,2,4,5,7,8"
6. In column D, create the new unique list:
   1. In D1, enter "=IF(C1<=ROW($A$6), INDEX($A$1:$A$6,C1), "")". And, the "if" is being used to stop the #REF case when the index goes beyond the end of the list.
   2. Use smart copy to populate D2:D6.
   3. The values of D1:D6 should now be "black","blue","green","red","","".

Hope this helps....

Upvotes: 53

Answer 21

You could use COUNTIF to get the number of occurence of the value in the range . So if the value is in A3, the range is A1:A6, then in the next column use a IF(EXACT(COUNTIF(A3:$A$6, A3),1), A3, ""). For the A4, it would be IF(EXACT(COUNTIF(A4:$A$6, A3),1), A4, "")

This would give you a column where all unique values are without any duplicate