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

  =unique(U3:U6)

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

https://www.listendata.com/2013/05/excel-3-ways-to-extract-unique-values.html

exceljet.net/formula/extract-unique-items-from-a-list

trumpexcel.com/unique-items-from-a-list-in-excel/

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?

Sep 22 in Others by Kithuzzz
• 20,660 points
89 views

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

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:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

answered Sep 23 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
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 in Others by narikkadan
• 37,660 points
38 views
0 votes
1 answer

Control cell value from another cell in Excel

You may accomplish this by using two ...READ MORE

answered Nov 10 in Others by narikkadan
• 37,660 points
44 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11 in Others by narikkadan
• 37,660 points
43 views
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 in Others by Edureka
• 13,640 points
145 views
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 in Others by Edureka
• 13,640 points
248 views
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 in Others by Edureka
• 13,640 points
112 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18 in Others by gaurav
• 22,040 points
76 views
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 in Others by narikkadan
• 37,660 points
65 views
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 in Others by narikkadan
• 37,660 points
36 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP