Results 1 to 8 of 8

Thread: Filter Multiple Selections Data Validation

  1. #1

    Filter Multiple Selections Data Validation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am trying to create a column in which each cell is with a drop down (data validation) where I can choose multiple options foe every cell and then I want to be able to filter the column by individual results.

    For instance if column A has the following cells:

    A1: header
    A2: apple, orange, grape
    A3: orange, apple
    A4: grape

    I want to use the auto-filter option so that if I filter to show only "orange", I will get A2 and A3. If I filter to show only "grape", I will get A2 and A4.

    How can this be done? Does anyone know?

    I managed to get the data validation drop down to enable to select multiple values in the same cell, by using VBA code:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Code:
    ' Developed by Contextures Inc.
    ' 
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    If Target.Count > 1 Then GoTo exitHandler
    
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    
    If rngDV Is Nothing Then GoTo exitHandler
    
    If Intersect(Target, rngDV) Is Nothing Then
    'do nothing
    Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 3 Then
    If oldVal = "" Then
    'do nothing
    Else
    If newVal = "" Then
    'do nothing
    Else
    Target.Value = oldVal _
    & ", " & newVal
    ' NOTE: you can use a line break,
    ' instead of a comma
    ' Target.Value = oldVal _
    ' & Chr(10) & newVal
    End If
    End If
    End If
    End If
    
    exitHandler:
    Application.EnableEvents = True
    End Sub
    However, I still can't filter it properly as I described in my initial question! Can anyone help? Perhaps I need another code to program the filter?

    Thank you very much!!!!
    Last edited by NBVC; 2013-11-25 at 06:24 PM.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    Since you're in column A you need to change:
    If Target.Column = 3 Then
    to:
    If Target.Column = 1 Then

    When autofiltering, use the 'contains' option of the custom filter, the code for which isalong the lines of:
    Range("A1").AutoFilter Field:=1, Criteria1:="=*grape*"
    Last edited by p45cal; 2013-11-28 at 01:46 PM.

  3. #3
    p4cal,

    thank you!


    Is there any way to program the auto filter of that column to show individual results in its auto filter drop down on the column header instead of multiple strings of multiple results?
    There are 20 different types of results in that column and users won’t know what word to enter in the custom filter’s “contain” feature. It would be much easier if they can just click on the column header’s auto-filter drop down and see all the individual results they can filter from and just check/uncheck which cells they want to show/filter.

    P4cal, do you know of any way to do that in VBA? Thank you!

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by yisfromer View Post
    There are 20 different types of results in that column and users won’t know what word to enter in the custom filter’s “contain” feature. It would be much easier if they can just click on the column header’s auto-filter drop down and see all the individual results they can filter from and just check/uncheck which cells they want to show/filter.

    P4cal, do you know of any way to do that in VBA?
    No, these are autofilter dropdowns and their contents are controlled by the built-in autofill code. There may be a workaround where you have a separate dropdown, say using Data Validation using the same list as the cells in column A, and the autofilter kind-of mirrors it?

    In the attached I have put such data validation in cell E1, and added code to the sheet change event handler to adjust the Autofilter. You could put this cell directly above the headers in column A instead of where it is now, though this would require small tweaks to the code.
    Attached Files Attached Files

  5. #5
    p45cal,

    Thank you so much. This is exactly what I was looking for! It would be just right if that cell would be in the place of the column header. Is that possible? What do you mean by "could put the cell directly above the headers"?

    Also, I do not know at all how to read VBA, I just right clicked on the sheet and selected "view code" and saw 7 different VBA windows. I don't know how to apply it to my Excel. Which of those codes to copy?

    Thank you very much,

    yisfromer

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by yisfromer View Post
    I do not know at all how to read VBA, I just right clicked on the sheet and selected "view code" and saw 7 different VBA windows. I don't know how to apply it to my Excel. Which of those codes to copy?
    Either attach your file to your next message and I will tweak it (but I won't necessarily be around on-demand to tweak it more later) or learn vba a bit more. There are some free resources on the net which will walk you through it:
    http://msdn.microsoft.com/en-us/libr...ffice.14).aspx
    http://excelvbatutor.com/vba_tutorial.html
    http://www.anthony-vba.kefra.com/vba/vbabasic1.htm
    http://www.excel-vba-easy.com/
    http://chandoo.org/wp/excel-vba/

    ps. Is this homework/an assignment?

  7. #7
    Hi p45cal,

    I do not take your assistance for granted. I am very thankful for your assistance. I see that I have permission to post attachments on the thread I started. However, is there a way to send the Excel to you privately? This Excel contains private information.

    This project is for a nonprofit organization assisting with AIDS and HIV testing and awareness.

    I am definitely going to look at those links, as the organization will require in the future some coding and I am the closest they have to an Excel specialist...Would you be interested in volunteering? The organization can definitely use your talents with Excel and any other technical skills you may have!

    Thank you very much!

    yisfromer

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    I have sent you a private message on this forum.

Posting Permissions

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