Extract unique value from the range in Excel

0 votes

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

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: https://exceljet.net/excel-functions/excel-unique-function


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




with INDEX: MATCH function


I also got nothing.

I also tried with IF statement also:


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


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

Sep 22, 2022 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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

enter image description here

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

To learn more about macros in general, see:


answered Sep 23, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Copy Text from Range in Excel into Word Document

Here are some articles that may help: Control ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
0 votes
1 answer

Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP