+ Reply to Thread
Results 1 to 9 of 9

howto select distinct values from list

  1. #1
    chris
    Guest

    howto select distinct values from list

    I have a list of the format:

    ITEM DATE
    ---- ----
    UK 200401
    UK 200402
    UK 200403
    UK 200404
    UK 200405
    IT 200401
    IT 200402
    ... ......

    The list is created from a database via MS Query, sort order 1,2.

    The vales for ITEM and DATE will vary each time the query is
    refreshed.

    I would like to setup a range that contains only a list of the grouped
    by ITEM. I could do this with another query (select distinct ITEM
    from X), but the queries take to long. How can I do this with excel
    functions?

    The required dataset (from the above example):

    ITEM
    ----
    UK
    IT

    Thanks in advance,

    Chris

  2. #2
    Trevor Shuttleworth
    Guest

    Re: howto select distinct values from list

    Chris

    use Advanced Filter, select unique items and copy to a new range

    Regards

    Trevor


    "chris" <[email protected]> wrote in message
    news:[email protected]...
    >I have a list of the format:
    >
    > ITEM DATE
    > ---- ----
    > UK 200401
    > UK 200402
    > UK 200403
    > UK 200404
    > UK 200405
    > IT 200401
    > IT 200402
    > .. ......
    >
    > The list is created from a database via MS Query, sort order 1,2.
    >
    > The vales for ITEM and DATE will vary each time the query is
    > refreshed.
    >
    > I would like to setup a range that contains only a list of the grouped
    > by ITEM. I could do this with another query (select distinct ITEM
    > from X), but the queries take to long. How can I do this with excel
    > functions?
    >
    > The required dataset (from the above example):
    >
    > ITEM
    > ----
    > UK
    > IT
    >
    > Thanks in advance,
    >
    > Chris




  3. #3
    Fadi Chalouhi
    Guest

    Re: howto select distinct values from list

    Hi Chris,

    You can create a UDF (IUser-Defined Function) to generate this list for
    you. Check this post :
    http://www.chalouhis.com/XLBLOG/arch.../unique-cells/

    HTH

    Fadi


  4. #4
    Biff
    Guest

    Re: howto select distinct values from list

    Hi!

    Here's one way:

    Assume:

    A1 = ITEM
    A2 = ----
    A3:An = UK, IT, etc..

    D1 = header ITEM
    D2 = ----
    D3 = empty, leave this cell empty
    D4 = formula entered with the key combo of CTRL,SHIFT,ENTER

    =IF(ISNA(MATCH(0,COUNTIF($D$3:D3,list),0)),"",INDEX(list,MATCH(0,COUNTIF($D$3:D3,list),0)))

    list is a defined dynamic range:

    =OFFSET(Sheet1!$A$3,,,COUNTA(Sheet1!$A:$A)-2,1)

    This assumes there will be no empty cells within the query list in column A.

    Now, you have to copy this formula down to enough cells that will cover the
    expected number of uniques returned. Will that be 100 cells? 500 cells?

    Biff

    "chris" <[email protected]> wrote in message
    news:[email protected]...
    >I have a list of the format:
    >
    > ITEM DATE
    > ---- ----
    > UK 200401
    > UK 200402
    > UK 200403
    > UK 200404
    > UK 200405
    > IT 200401
    > IT 200402
    > .. ......
    >
    > The list is created from a database via MS Query, sort order 1,2.
    >
    > The vales for ITEM and DATE will vary each time the query is
    > refreshed.
    >
    > I would like to setup a range that contains only a list of the grouped
    > by ITEM. I could do this with another query (select distinct ITEM
    > from X), but the queries take to long. How can I do this with excel
    > functions?
    >
    > The required dataset (from the above example):
    >
    > ITEM
    > ----
    > UK
    > IT
    >
    > Thanks in advance,
    >
    > Chris




  5. #5
    keepITcool
    Guest

    Re: howto select distinct values from list

    Fadi,

    The code may come from JWalk but is painfully slow
    It takes 14 seconds on an array of 1000 elements..
    dont think of running it on 2000 or more.

    Following will work effortless with large arrays, and returns
    5000 sorted uniques from 60000 text set in under .5 seconds.


    Option Explicit
    Option Compare Text

    Public Function Uniques(ByVal vSourceArray As Variant, _
    Optional ByVal Sorted As Byte, _
    Optional ByVal CountOnly As Boolean)
    'author:keepITcool

    'Requires Ref to Microsoft Scripting Runtime
    Dim oDic As Dictionary
    Dim n&, l&, v, itm

    'Initialize the dictionary
    Set oDic = New Dictionary
    oDic.CompareMode = TextCompare

    'Exit if no array
    If Not IsArray(vSourceArray) Then GoTo theExit
    'Take values if Range
    If TypeName(vSourceArray) = "Range" Then vSourceArray = vSourceArray

    'Key must be unique, so doubles give (ignored) errors
    On Error Resume Next
    For Each itm In vSourceArray
    oDic.Add itm, itm
    Next
    'Quicker then testing for empties, just remove it
    oDic.Remove vbNullString

    On Error GoTo theError

    If CountOnly Then
    v = oDic.Count
    Else
    v = oDic.Items
    'make 1based for compatibility
    ReDim Preserve v(1 To UBound(v) - LBound(v) + 1)
    Select Case Sorted
    Case Is > 0: Call QSort(v, xlAscending)
    Case Is < 0: Call QSort(v, xlDescending)
    End Select
    End If

    theExit:
    Uniques = v
    Exit Function
    theError:
    Uniques = CVErr(xlErrValue)

    End Function

    Public Sub QSort(v, _
    Optional SortOrder As XlSortOrder = xlAscending, _
    Optional n& = True, Optional m& = True)
    Dim i&, j&, p, t
    If n = True Then n = LBound(v)
    If m = True Then m = UBound(v)
    i = n: j = m: p = v((n + m) \ 2)
    While (i <= j)
    While (v(i) < p And i < m): i = i + 1: Wend
    While (v(j) > p And j > n): j = j - 1: Wend
    If (i <= j) Then
    t = v(i): v(i) = v(j): v(j) = t
    i = i + 1: j = j - 1
    End If
    Wend
    If (n < j) Then QSort v, SortOrder, n, j
    If (i < m) Then QSort v, SortOrder, i, m
    End Sub


    --
    keepITcool
    | www.XLsupport.com | keepITcool chello nl | amsterdam


    Fadi Chalouhi wrote :

    > Hi Chris,
    >
    > You can create a UDF (IUser-Defined Function) to generate this list
    > for you. Check this post :
    > http://www.chalouhis.com/XLBLOG/arch.../unique-cells/
    >
    > HTH
    >
    > Fadi


  6. #6
    Biff
    Guest

    Re: howto select distinct values from list

    Hi!

    What are the arguments to this udf?

    The first is obvious. The third looks to be a boolean TRUE or FALSE but what
    does TRUE or FALSE mean? I don't have a clue about the 2nd.

    Biff

    "keepITcool" <[email protected]> wrote in message
    news:[email protected]...
    > Fadi,
    >
    > The code may come from JWalk but is painfully slow
    > It takes 14 seconds on an array of 1000 elements..
    > dont think of running it on 2000 or more.
    >
    > Following will work effortless with large arrays, and returns
    > 5000 sorted uniques from 60000 text set in under .5 seconds.
    >
    >
    > Option Explicit
    > Option Compare Text
    >
    > Public Function Uniques(ByVal vSourceArray As Variant, _
    > Optional ByVal Sorted As Byte, _
    > Optional ByVal CountOnly As Boolean)
    > 'author:keepITcool
    >
    > 'Requires Ref to Microsoft Scripting Runtime
    > Dim oDic As Dictionary
    > Dim n&, l&, v, itm
    >
    > 'Initialize the dictionary
    > Set oDic = New Dictionary
    > oDic.CompareMode = TextCompare
    >
    > 'Exit if no array
    > If Not IsArray(vSourceArray) Then GoTo theExit
    > 'Take values if Range
    > If TypeName(vSourceArray) = "Range" Then vSourceArray = vSourceArray
    >
    > 'Key must be unique, so doubles give (ignored) errors
    > On Error Resume Next
    > For Each itm In vSourceArray
    > oDic.Add itm, itm
    > Next
    > 'Quicker then testing for empties, just remove it
    > oDic.Remove vbNullString
    >
    > On Error GoTo theError
    >
    > If CountOnly Then
    > v = oDic.Count
    > Else
    > v = oDic.Items
    > 'make 1based for compatibility
    > ReDim Preserve v(1 To UBound(v) - LBound(v) + 1)
    > Select Case Sorted
    > Case Is > 0: Call QSort(v, xlAscending)
    > Case Is < 0: Call QSort(v, xlDescending)
    > End Select
    > End If
    >
    > theExit:
    > Uniques = v
    > Exit Function
    > theError:
    > Uniques = CVErr(xlErrValue)
    >
    > End Function
    >
    > Public Sub QSort(v, _
    > Optional SortOrder As XlSortOrder = xlAscending, _
    > Optional n& = True, Optional m& = True)
    > Dim i&, j&, p, t
    > If n = True Then n = LBound(v)
    > If m = True Then m = UBound(v)
    > i = n: j = m: p = v((n + m) \ 2)
    > While (i <= j)
    > While (v(i) < p And i < m): i = i + 1: Wend
    > While (v(j) > p And j > n): j = j - 1: Wend
    > If (i <= j) Then
    > t = v(i): v(i) = v(j): v(j) = t
    > i = i + 1: j = j - 1
    > End If
    > Wend
    > If (n < j) Then QSort v, SortOrder, n, j
    > If (i < m) Then QSort v, SortOrder, i, m
    > End Sub
    >
    >
    > --
    > keepITcool
    > | www.XLsupport.com | keepITcool chello nl | amsterdam
    >
    >
    > Fadi Chalouhi wrote :
    >
    >> Hi Chris,
    >>
    >> You can create a UDF (IUser-Defined Function) to generate this list
    >> for you. Check this post :
    >> http://www.chalouhis.com/XLBLOG/arch.../unique-cells/
    >>
    >> HTH
    >>
    >> Fadi




  7. #7
    keepITcool
    Guest

    Re: howto select distinct values from list


    I hoped it would be obvious from the code

    Sorted =0 or omitted, not sorted
    Sorted >0, xlAscending
    Sorted <0, xlDescending

    CountOnly = true
    Do not return the array of unique items, but the count only


    --
    keepITcool
    | www.XLsupport.com | keepITcool chello nl | amsterdam


    Biff wrote :

    > Hi!
    >
    > What are the arguments to this udf?
    >
    > The first is obvious. The third looks to be a boolean TRUE or FALSE
    > but what does TRUE or FALSE mean? I don't have a clue about the 2nd.
    >
    > Biff
    >
    > "keepITcool" <[email protected]> wrote in message
    > news:[email protected]...
    > > Fadi,
    > >
    > > The code may come from JWalk but is painfully slow
    > > It takes 14 seconds on an array of 1000 elements..
    > > dont think of running it on 2000 or more.
    > >
    > > Following will work effortless with large arrays, and returns
    > > 5000 sorted uniques from 60000 text set in under .5 seconds.
    > >
    > >
    > > Option Explicit
    > > Option Compare Text
    > >
    > > Public Function Uniques(ByVal vSourceArray As Variant, _
    > > Optional ByVal Sorted As Byte, _
    > > Optional ByVal CountOnly As Boolean)
    > > 'author:keepITcool
    > >
    > > 'Requires Ref to Microsoft Scripting Runtime
    > > Dim oDic As Dictionary
    > > Dim n&, l&, v, itm
    > >
    > > 'Initialize the dictionary
    > > Set oDic = New Dictionary
    > > oDic.CompareMode = TextCompare
    > >
    > > 'Exit if no array
    > > If Not IsArray(vSourceArray) Then GoTo theExit
    > > 'Take values if Range
    > > If TypeName(vSourceArray) = "Range" Then vSourceArray =
    > > vSourceArray
    > >
    > > 'Key must be unique, so doubles give (ignored) errors
    > > On Error Resume Next
    > > For Each itm In vSourceArray
    > > oDic.Add itm, itm
    > > Next
    > > 'Quicker then testing for empties, just remove it
    > > oDic.Remove vbNullString
    > >
    > > On Error GoTo theError
    > >
    > > If CountOnly Then
    > > v = oDic.Count
    > > Else
    > > v = oDic.Items
    > > 'make 1based for compatibility
    > > ReDim Preserve v(1 To UBound(v) - LBound(v) + 1)
    > > Select Case Sorted
    > > Case Is > 0: Call QSort(v, xlAscending)
    > > Case Is < 0: Call QSort(v, xlDescending)
    > > End Select
    > > End If
    > >
    > > theExit:
    > > Uniques = v
    > > Exit Function
    > > theError:
    > > Uniques = CVErr(xlErrValue)
    > >
    > > End Function
    > >
    > > Public Sub QSort(v, _
    > > Optional SortOrder As XlSortOrder = xlAscending, _
    > > Optional n& = True, Optional m& = True)
    > > Dim i&, j&, p, t
    > > If n = True Then n = LBound(v)
    > > If m = True Then m = UBound(v)
    > > i = n: j = m: p = v((n + m) \ 2)
    > > While (i <= j)
    > > While (v(i) < p And i < m): i = i + 1: Wend
    > > While (v(j) > p And j > n): j = j - 1: Wend
    > > If (i <= j) Then
    > > t = v(i): v(i) = v(j): v(j) = t
    > > i = i + 1: j = j - 1
    > > End If
    > > Wend
    > > If (n < j) Then QSort v, SortOrder, n, j
    > > If (i < m) Then QSort v, SortOrder, i, m
    > > End Sub
    > >
    > >
    > > --
    > > keepITcool
    > > > www.XLsupport.com | keepITcool chello nl | amsterdam

    > >
    > >
    > > Fadi Chalouhi wrote :
    > >
    > >> Hi Chris,
    > > >
    > >> You can create a UDF (IUser-Defined Function) to generate this list
    > >> for you. Check this post :
    > >> http://www.chalouhis.com/XLBLOG/arch.../unique-cells/
    > > >
    > >> HTH
    > > >
    > >> Fadi


  8. #8
    Registered User
    Join Date
    04-14-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    127

    Re: howto select distinct values from list

    Hi all,

    Can someone please help me.

    Is this solution works for distinct positive values.
    I mean if there are values like -1, 5, #REF, 5, 1

    Answer should be 2.

    Regards,
    Vikas

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: howto select distinct values from list

    @kisanvikas2015,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

    (link above in the menu bar)
    Last edited by protonLeah; 06-05-2015 at 12:26 AM.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1