Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter (2000 SP3)

    I need to create a filter for column2 of attached file. The problem is that some of the cells contain numbers and others contain lists of numbers - eg 3, 14, 16, 28. The user wants to supply a number - eg 16 and see all rows containing that class whether singly or as part of a list. I understand that it will require a VBA solution. Does anyone have any ideas?

    Thanks
    Ruth

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filter (2000 SP3)

    You may not need VBA as you can set a custom filter interactively through use of the AutoFilter settings.
    Try the settings shown on the attached graphic.

    Andrew C

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter (2000 SP3)

    That will work fine for 16, but if you use the value 3, this filter will find both 3 and 23 (and all other items containing the digit 3)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filter (2000 SP3)

    This will prompt for a entry and filter for the number or as text within the string.

    <pre>Sub FilterButton()
    Dim sValue As String
    sValue = InputBox("What Value to Filter")

    Range("a1").AutoFilter Field:=2, _
    Criteria1:="=" & sValue, Operator:=xlOr, _
    Criteria2:="=*" & sValue & "*"
    End Sub</pre>


    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filter (2000 SP3)

    Quite correct Hans.

    Should have thought a bit more about it.

    Andrew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter (2000 SP3)

    Steve, your VBA solution seems to have the same problem as Andrew's interactive one.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filter (2000 SP3)

    Yes it does, since it is just the macro version of Andrew's suggestions
    I put it in as an example. I do not think there is an "easy way" around it. I am not sure you could do it "Direclty" using filter since you are limited to 2 criteria. You could create intermediate columns, to do the "contain" and eliminate the " x" items and ",x" items, but it is problematic, since you are searching for "contains".

    Ideally the data would be setup more "realationally" with a separate table so that you would look up in the "linked" table to get the appropriate row #s to filter out, but this is a redesign of the database and more problematic (with excel) to maintain integrity.

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter (2000 SP3)

    Perhaps the attached will do. It uses a custom VBA function

    Function Contains(strText, strPart) As Boolean
    Contains = _
    strPart = strText Or _
    Right(strText, Len(strPart) + 1) = " " & strPart Or _
    Left(strText, Len(strPart) + 1) = strPart & "," Or _
    InStr(strText, " " & strPart & ",") > 0
    End Function

    It can also be done with formulas, if you prefer. I added an intermediate column "test", and a separate cell containing the value to filter on (A35). You can use AutoFilter, but you would have to redo it each time you change the filter cell; this is not what users expect. Or you can use Advanced Filter.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filter (2000 SP3)

    An enhancement:
    If you name cell Sheet1!A35 as "Class", and add this routine to the activesheet object, and add autofilter,

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("class")) Is Nothing Then
    If ActiveSheet.AutoFilterMode Then _
    ActiveSheet.AutoFilter.Range.AutoFilter Field:=3, Criteria1:="TRUE"
    End If
    End Sub</pre>


    when you change the class the filter will be done automatically. If this is done, I would also suggest moving the filtered range down several rows and move rows A34 and A35 to the top and Freeze the panes at the autofilter headers to keep the "selection" in view.

    Steve

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter (2000 SP3)

    Excellent ideas! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  11. #11
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter (2000 SP3)

    Hi Andrew

    Thanks for this reply - I had already tried this and come up with the problem Hans identified.
    Thanks anyway
    Ruth

  12. #12
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter (2000 SP3)

    Hi Hans

    Thanks so much for this - it was exactly what I needed. I now have it filtering as the users required.

    Thanks
    Ruth

  13. #13
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter (2000 SP3)

    Hi Steve
    Thanks very much for your help. Between all the suggestions I have what I needed.
    Thanks
    Ruth

Posting Permissions

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