Filter with multiple criteria dropdown list

[hershey17]

Hello,
I am currently working on a spreadsheet with many data, including a dropdown list with multiple selection (I used a VBA code to activate it). Now, on another sheet, I need to extract data from the initial one by using the dropdown list as a criteria, but the advance filter is not considering the word that is not in the beginning of the cell for example: criteria needed is red (ie. Cell with selected dropdown => red, blue, green and another one with selected dropdown => yellow, red, orange). The advanced filter will only take the data with "red" in the beginning but not the other one even if it contains "red".

What do you suggest I can do to filter and extract data according to a multiple dropdown containing the criteria anywhere in the cell?

thank you in advance.

 

[Tom.Jones]

Hi,

Attach the file so we can see the data structure. A change to the VBA code may be required.

 

[hershey17]

Hi,

Attach the file so we can see the data structure. A change to the VBA code may be required.

Hello,

Here's what the data looks like. I have a separate list of "Groups" which is the source of column C (dropdown list).

After the advanced filter, I get these:

This is the code that I found to enable multiple selection in a dropdown list:

Private Sub Worksheet_Change(ByVal Target As Range)
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 3 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub