Results 1 to 5 of 5

Thread: Extract Unique Values List

  1. #1
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14

    Lightbulb Extract Unique Values List

    if you want to Extract Unique Value From a List , you can use this UDF :

    Code:
    Function UniqueList(rng As Range, Pos As Long) As String
        
        Dim List() As String
        Dim cell As Range
        Dim i As Long
        Dim t As Long
        i = 0
    
        ReDim List(rng.Cells.Count) As String
        For Each cell In rng
            flag = 0
            For t = LBound(List) To UBound(List)
                If cell.Value = List(t) Then
                    flag = 1
                    Exit For
                End If
            Next
            If flag = 0 Then
                List(i) = cell.Value
                i = i + 1
            End If
        Next
        UniqueList = List(Pos)
    
    End Function

  2. #2
    Junior Member
    Join Date
    May 2011
    Posts
    3
    Rep Power
    0

    Unhappy How would I get list of unique value

    Hi Rajan,

    How would I get list of Uniqe value though its return only single value.

    Rajiv

    Quote Originally Posted by Rajan_Verma View Post
    if you want to Extract Unique Value From a List , you can use this UDF :


    Code:
    Function UniqueList(rng As Range, Pos As Long) As String
        
        Dim List() As String
        Dim cell As Range
        Dim i As Long
        Dim t As Long
        i = 0
    
        ReDim List(rng.Cells.Count) As String
        For Each cell In rng
            flag = 0
            For t = LBound(List) To UBound(List)
                If cell.Value = List(t) Then
                    flag = 1
                    Exit For
                End If
            Next
            If flag = 0 Then
                List(i) = cell.Value
                i = i + 1
            End If
        Next
        UniqueList = List(Pos)
    
    End Function

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    You are right Rajiv..... we probably didn't need to have an index in the function. But it can be tweaked to give your result though

    Code:
    Function UniqueList(rng As Range, Optional Pos As String) As Variant
        
        Dim List As Variant
        Dim cell As Range
        Dim i As Long
        Dim t As Long
        Dim flag As Long
        i = 0
    
        ReDim List(rng.Cells.Count) As Variant
        For Each cell In rng
            flag = 0
            For t = LBound(List) To UBound(List)
                If cell.Value = List(t) Then
                    flag = 1
                    Exit For
                End If
            Next
            If flag = 0 Then
                List(i) = cell.Value
                i = i + 1
            End If
        Next
        ReDim Preserve List(i - 1)
        If Pos <> "" Then
            UniqueList = List(CLng(Pos))
        Else
            UniqueList = List
        End If
        
    End Function
    Sub ExampleOfHowToUseIt()
    
        MsgBox Join(UniqueList(rngRange), ",")'When it's an array
        MsgBox UniqueList(rngRange, 1)'When you've passed the index of the element, it gives just one value
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Hi Rajeev,

    my function is not an array function, it required an index parameter.. like if you are using this in B1 . you can use that like this :
    where A1:A12 is your list

    =UniqueList($A$1:$A$12,ROW()-1)

  5. #5
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    Here is an array function also

    Function GetUniqueList(rng As Range) As Variant

    On Error Resume Next

    Dim Arr() As Variant
    Dim cell As Range
    Dim r as Integer, c As Integer
    Dim i as integer, j As Integer
    i = 0: j = 0

    With Application.Caller
    r = .Rows.Count
    c = .Columns.Count
    End With
    ReDim Arr(r - 1, c - 1)

    For Each cell In rng
    If WorksheetFunction.CountIf(rng.Cells(1, 1).Resize(cell.Row, 1), cell.Value) = 1 Then
    Arr(i, j) = cell.Value
    If j = c Then j = j + 1
    i = i + 1
    End If

    For k = i To UBound(Arr())
    Arr(k, 0) = ""
    Next
    Next
    GetUniqueList = Arr
    End Function

Similar Threads

  1. Numbered List Of Unique Values
    By xander1981 in forum Excel Help
    Replies: 6
    Last Post: 01-21-2013, 06:10 PM
  2. List Unique Values Using Formula
    By LalitPandey87 in forum Excel Help
    Replies: 5
    Last Post: 01-09-2012, 08:39 PM
  3. Replies: 2
    Last Post: 01-07-2012, 12:11 AM
  4. List Unique/Common Values From Two Ranges
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 09-16-2011, 08:34 AM
  5. Extract Unique Values From a Range
    By Admin in forum Download Center
    Replies: 0
    Last Post: 05-13-2011, 10:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •