Extract unique value from the range in Excel

Question

I want to use Excel to extract the distinct value from my range. Between the columns in this range, this value will fluctuate.

Between the N/A values, I have got the proper address, which I would like to have populated in the cell pointed below.

For this purpose I tried a UNIQUE function

  =unique(U3:U6)

Which gave me the #NAME value. I also tried the other code from here:

with INDEX: MATCH function

   =IFERROR(INDEX($U$3:$U$6,MATCH(0,COUNTIF($U$3:UE,$U$3:$U$6),0)),"")

I also got nothing.

I also tried with IF statement also:

     =IF(U3:U6,"N/A+N/A+N/A","")

But I don't know how to extract the cell with other values than stated in the formula.

The combination of function INDEX:MATCH with COUNTIF also doesn't work

  =INDEX(List,MATCH(0,COUNTIF(U3:$U$6,List)+(COUNTIF(List,List)<>1),0))

How can I make my unique value visible in the cell below? How can I exclude all not valid records in this range?

Answer 1

The portability of spreadsheet functions like UNIQUE() is a challenge. They are only supported by the latest versions of Excel. Utilizing a VBA User Defined Function is an additional choice. Excel supports VBA in Paleozoic editions as well (all you need to do is make sure macros are enabled)

Here is a typical UDF to return unique values:

Public Function unikue(rng As Range)
    Dim arr, c As Collection, r As Range
    Dim nCall As Long, nColl As Long
    Dim i As Long
    Set c = New Collection

    nCall = Application.Caller.Count

    On Error Resume Next
        For Each r In rng
            c.Add r.Text, CStr(r.Text)
        Next r
    On Error GoTo 0
    nColl = c.Count


    If nCall > nColl Then
        ReDim arr(1 To nCall, 1 To 1)
        For i = 1 To nCall
            arr(i, 1) = ""
        Next i
    Else
        ReDim arr(1 To nColl, 1 To 1)
    End If

    For i = 1 To nColl
        arr(i, 1) = c.Item(i)
    Next i

    unikue = arr
End Function

For example:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later than 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use this UDF in the worksheet:

1. hi-light a block of cells in some column
2. click in the Formula Bar
3. type: =UNIKUE(U3:U6)
4. finish with Cntrl-Shft-ENTER rather than just the ENTER key