Filter Multiple Selections Data Validation

Y

yisfromer

New member
Joined
Nov 25, 2013
Messages
4
Reaction score
0
Points
0
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)[/COLOR]
[COLOR=#333333]' Developed by Contextures Inc.[/COLOR]
[COLOR=#333333]' [/COLOR]
[COLOR=#333333]Dim rngDV As Range[/COLOR]
[COLOR=#333333]Dim oldVal As String[/COLOR]
[COLOR=#333333]Dim newVal As String[/COLOR]
[COLOR=#333333]If Target.Count > 1 Then GoTo exitHandler[/COLOR]

[COLOR=#333333]On Error Resume Next[/COLOR]
[COLOR=#333333]Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)[/COLOR]
[COLOR=#333333]On Error GoTo exitHandler[/COLOR]

[COLOR=#333333]If rngDV Is Nothing Then GoTo exitHandler[/COLOR]

[COLOR=#333333]If Intersect(Target, rngDV) Is Nothing Then[/COLOR]
[COLOR=#333333]'do nothing[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Application.EnableEvents = False[/COLOR]
[COLOR=#333333]newVal = Target.Value[/COLOR]
[COLOR=#333333]Application.Undo[/COLOR]
[COLOR=#333333]oldVal = Target.Value[/COLOR]
[COLOR=#333333]Target.Value = newVal[/COLOR]
[COLOR=#333333]If Target.Column = 3 Then[/COLOR]
[COLOR=#333333]If oldVal = "" Then[/COLOR]
[COLOR=#333333]'do nothing[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]If newVal = "" Then[/COLOR]
[COLOR=#333333]'do nothing[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Target.Value = oldVal _[/COLOR]
[COLOR=#333333]& ", " & newVal[/COLOR]
[COLOR=#333333]' NOTE: you can use a line break,[/COLOR]
[COLOR=#333333]' instead of a comma[/COLOR]
[COLOR=#333333]' Target.Value = oldVal _[/COLOR]
[COLOR=#333333]' & Chr(10) & newVal[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]

[COLOR=#333333]exitHandler:[/COLOR]
[COLOR=#333333]Application.EnableEvents = True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

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 a moderator:
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:
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!
 
yisfromer said:
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?
Click to expand...
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.
 

Attachments

  • ExcelGuru2392.xls
    32.5 KB · Views: 124
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
 
yisfromer said:
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?
Click to expand...
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/library/ee814737(v=office.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?
 
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
 
I have sent you a private message on this forum.
 
You must log in or register to reply here.
Back
Top