Getting unique values in Excel by using formulas o

Question

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

E.g: a 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

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 2

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 3

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 4

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 5

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 6

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