Filtering multiple columns w/ Multiple criteria

[cspengel]

Hello, I have searched this forum and google and couldn't exactly find what I am looking for. I feel like I asked this a year or so ago and trying to re-create a workbook I lost.

I am using a macro from this page: All Combinations of Multiple Columns Without Duplicates

My issue is filtering my data has become a massive headache as changing the filter in one column removes the information needed in another column.

i.e say I was searching for the name David in column B, but david is also in column C. I then lost that entry. The information cannot simply be filtered using advanced filter as the information i want to filter is ever changing and I may not know what I want to filter until I see the name.
Is it possible to have a drop down list added automatically that contains all the names (no duplicates) in my dataset. Then i just click on the names and the set will filter?

If you look at my image (I have my helper columns showing), I want the following to occur:

-if i select one or more names to filter, all rows containing selected names(regardless of column) will appear. Thanks for your help!

 

[cspengel]

Anyone? If I recall maybe something with a combo box and a list of names. If that's the case, how would I sort the row vba to filter multiple names. Thanks!

 

[cspengel]

 

[kevin9999]

You'd probably get a better response if you provided a sample of your data with the XL2BB add in so we wouldn't have to type in/create a data set to test the code. With that in mind, I've provided a demonstration below that does what I think you are looking for. Obviously you'll need to adjust references/sheet names etc. It is provided for demonstration purposes only. Credit to TrumpExcel for the worksheet_change code.

With this on sheet1

cspengel.xlsm
	A	B	C	D
1	Name1	Name2	Name3	Name4
2	Adam	Bill	Helen	Steve
3	Bill	Chris	Irene	Tracy
4	Chris	David	Bill	Ursula
5	David	Eric	Chris	Vanessa
6	Eric	Francis	David	Bill
7	Francis	Gillian	Eric	Chris
8	Gillian	Helen	Francis	David
9	Helen	Irene	Gillian	Eric
10	Irene	Jackie	Helen	Francis
11	Jackie	Adam	Irene	Gillian
Sheet1

And this on sheet2

cspengel.xlsm
	C	D	E
1	Dropdown		Unique Names List
2	Adam		Adam
3			Bill
4			Chris
5			David
6			Eric
7			Francis
8			Gillian
9			Helen
10			Irene
11			Jackie
12			Steve
13			Tracy
14			Ursula
15			Vanessa
16
Sheet2

Cell Formulas
Range	Formula
E2:E15	E2	=SORT(UNIQUE(VSTACK(Sheet1!A2:A11,Sheet1!B2:B11,Sheet1!C2:C11,Sheet1!D2:D11)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$E$2#

With this worksheet_change code for sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' 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.Address = "$C$2" 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
Exitsub:
    Application.EnableEvents = True
End Sub

When you run this code in a standard module

Option Explicit
Sub cspengel()
    Application.ScreenUpdating = False
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Set Ws1 = Worksheets("Sheet1")
    Set Ws2 = Worksheets("Sheet2")
    Dim LRow As Long, i As Long, j As Long
    
    Dim MyName As String, x As String, Arr
    MyName = Ws2.Range("C2")
    If MyName <> "" Then
        Arr = Split(MyName)
        
        Ws1.Cells.EntireRow.Hidden = False
        LRow = Ws1.Cells(Rows.Count, 1).End(3).Row
        Ws1.Range(Ws1.Cells(2, 1), Ws1.Cells(LRow, 1)).EntireRow.Hidden = True
        
        For i = LBound(Arr) To UBound(Arr)
            x = WorksheetFunction.Trim(Arr(i))
            For j = 2 To LRow
                If WorksheetFunction.CountIf(Ws1.Cells(j, 1).EntireRow, x) > 0 Then Ws1.Cells(j, 1).EntireRow.Hidden = False
            Next j
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

You will get this result when you select "Adam" in the dropdown on sheet2 cell C2

cspengel.xlsm
	A	B	C	D
1	Name1	Name2	Name3	Name4
2	Adam	Bill	Helen	Steve
11	Jackie	Adam	Irene	Gillian
12
Sheet1

You can also select multiple names in the dropdown on sheet2 cell C2, like this:

cspengel.xlsm
	C	D	E
1	Dropdown		Unique Names List
2	Adam Chris		Adam
3			Bill
4			Chris
5			David
6			Eric
7			Francis
8			Gillian
9			Helen
10			Irene
11			Jackie
12			Steve
13			Tracy
14			Ursula
15			Vanessa
16
Sheet2

Cell Formulas
Range	Formula
E2:E15	E2	=SORT(UNIQUE(VSTACK(Sheet1!A2:A11,Sheet1!B2:B11,Sheet1!C2:C11,Sheet1!D2:D11)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$E$2#

When you run the code, you get the following on sheet1

cspengel.xlsm
	A	B	C	D
1	Name1	Name2	Name3	Name4
2	Adam	Bill	Helen	Steve
3	Bill	Chris	Irene	Tracy
4	Chris	David	Bill	Ursula
5	David	Eric	Chris	Vanessa
7	Francis	Gillian	Eric	Chris
11	Jackie	Adam	Irene	Gillian
12
Sheet1

I think that's the sort of thing you were looking for?

 

[cspengel]

You'd probably get a better response if you provided a sample of your data with the XL2BB add in so we wouldn't have to type in/create a data set to test the code. With that in mind, I've provided a demonstration below that does what I think you are looking for. Obviously you'll need to adjust references/sheet names etc. It is provided for demonstration purposes only. Credit to TrumpExcel for the worksheet_change code.

With this on sheet1

cspengel.xlsm
	A	B	C	D
1	Name1	Name2	Name3	Name4
2	Adam	Bill	Helen	Steve
3	Bill	Chris	Irene	Tracy
4	Chris	David	Bill	Ursula
5	David	Eric	Chris	Vanessa
6	Eric	Francis	David	Bill
7	Francis	Gillian	Eric	Chris
8	Gillian	Helen	Francis	David
9	Helen	Irene	Gillian	Eric
10	Irene	Jackie	Helen	Francis
11	Jackie	Adam	Irene	Gillian
Sheet1

And this on sheet2

cspengel.xlsm
	C	D	E
1	Dropdown		Unique Names List
2	Adam		Adam
3			Bill
4			Chris
5			David
6			Eric
7			Francis
8			Gillian
9			Helen
10			Irene
11			Jackie
12			Steve
13			Tracy
14			Ursula
15			Vanessa
16
Sheet2

Cell Formulas
Range	Formula
E2:E15	E2	=SORT(UNIQUE(VSTACK(Sheet1!A2:A11,Sheet1!B2:B11,Sheet1!C2:C11,Sheet1!D2:D11)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$E$2#

With this worksheet_change code for sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' 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.Address = "$C$2" 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
Exitsub:
    Application.EnableEvents = True
End Sub

When you run this code in a standard module

Option Explicit
Sub cspengel()
    Application.ScreenUpdating = False
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Set Ws1 = Worksheets("Sheet1")
    Set Ws2 = Worksheets("Sheet2")
    Dim LRow As Long, i As Long, j As Long
   
    Dim MyName As String, x As String, Arr
    MyName = Ws2.Range("C2")
    If MyName <> "" Then
        Arr = Split(MyName)
       
        Ws1.Cells.EntireRow.Hidden = False
        LRow = Ws1.Cells(Rows.Count, 1).End(3).Row
        Ws1.Range(Ws1.Cells(2, 1), Ws1.Cells(LRow, 1)).EntireRow.Hidden = True
       
        For i = LBound(Arr) To UBound(Arr)
            x = WorksheetFunction.Trim(Arr(i))
            For j = 2 To LRow
                If WorksheetFunction.CountIf(Ws1.Cells(j, 1).EntireRow, x) > 0 Then Ws1.Cells(j, 1).EntireRow.Hidden = False
            Next j
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

You will get this result when you select "Adam" in the dropdown on sheet2 cell C2

cspengel.xlsm
	A	B	C	D
1	Name1	Name2	Name3	Name4
2	Adam	Bill	Helen	Steve
11	Jackie	Adam	Irene	Gillian
12
Sheet1

You can also select multiple names in the dropdown on sheet2 cell C2, like this:

cspengel.xlsm
	C	D	E
1	Dropdown		Unique Names List
2	Adam Chris		Adam
3			Bill
4			Chris
5			David
6			Eric
7			Francis
8			Gillian
9			Helen
10			Irene
11			Jackie
12			Steve
13			Tracy
14			Ursula
15			Vanessa
16
Sheet2

Cell Formulas
Range	Formula
E2:E15	E2	=SORT(UNIQUE(VSTACK(Sheet1!A2:A11,Sheet1!B2:B11,Sheet1!C2:C11,Sheet1!D2:D11)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$E$2#

When you run the code, you get the following on sheet1

cspengel.xlsm
	A	B	C	D
1	Name1	Name2	Name3	Name4
2	Adam	Bill	Helen	Steve
3	Bill	Chris	Irene	Tracy
4	Chris	David	Bill	Ursula
5	David	Eric	Chris	Vanessa
7	Francis	Gillian	Eric	Chris
11	Jackie	Adam	Irene	Gillian
12
Sheet1

I think that's the sort of thing you were looking for?

Really appreciate your time with this. Was losing my mind and Been searching for days for a solution. In the future I will add a workbook my apologies.

With the code your provided, is it possible to to know which rows contain multiple names I select from drop list . So if I I chose Adam and Chris, could I apply some sort of color conditioning to differentiate which rows contain 1 name and which contain multiple? Sorry if that's confusing .

 

[kevin9999]

Try the following - just change the colors to whatever you want.

Option Explicit
Sub cspengel_2()
    Application.ScreenUpdating = False
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Set Ws1 = Worksheets("Sheet1")
    Set Ws2 = Worksheets("Sheet2")
    Dim LRow As Long, LCol As Long, i As Long, j As Long, k As Long
    
    Dim MyName As String, x As String, Arr
    MyName = Ws2.Range("C2")
    If MyName <> "" Then
        Arr = Split(MyName)
        
        Ws1.Cells.EntireRow.Hidden = False
        Ws1.UsedRange.Offset(1).Interior.Color = xlNone
        LRow = Ws1.Cells(Rows.Count, 1).End(3).Row
        LCol = Ws1.Cells(1, Columns.Count).End(xlToLeft).Column
        Ws1.Range(Ws1.Cells(2, 1), Ws1.Cells(LRow, 1)).EntireRow.Hidden = True
        
        For i = LBound(Arr) To UBound(Arr)
            x = WorksheetFunction.Trim(Arr(i))
            For j = 2 To LRow
                If WorksheetFunction.CountIf(Ws1.Cells(j, 1).EntireRow, x) > 0 Then Ws1.Cells(j, 1).EntireRow.Hidden = False
            Next j
        Next i
        
        For j = 2 To LRow
            For i = LBound(Arr) To UBound(Arr)
            x = WorksheetFunction.Trim(Arr(i))
                If WorksheetFunction.CountIf(Ws1.Cells(j, 1).EntireRow, x) > 0 Then k = k + 1
            Next i
            With Ws1.Range(Ws1.Cells(j, 1), Ws1.Cells(j, LCol)).Interior
                If k = 1 Then
                    .ThemeColor = xlThemeColorAccent4
                    .TintAndShade = 0.799981688894314
                ElseIf k > 1 Then
                    .ThemeColor = xlThemeColorAccent5
                    .TintAndShade = 0.799981688894314
                End If
            End With
            k = 0
        Next j
        
    End If
    Application.ScreenUpdating = True
End Sub

Leads to this result (using your example scenario)

cspengel.xlsm
	A	B	C	D
1	Name1	Name2	Name3	Name4
2	Adam	Bill	Helen	Steve
3	Bill	Chris	Adam	Tracy
4	Chris	David	Bill	Ursula
5	David	Eric	Chris	Vanessa
7	Adam	Gillian	Eric	Chris
11	Jackie	Adam	Irene	Gillian
12
Sheet1

 

[cspengel]

Try the following - just change the colors to whatever you want.

Option Explicit
Sub cspengel_2()
    Application.ScreenUpdating = False
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Set Ws1 = Worksheets("Sheet1")
    Set Ws2 = Worksheets("Sheet2")
    Dim LRow As Long, LCol As Long, i As Long, j As Long, k As Long
   
    Dim MyName As String, x As String, Arr
    MyName = Ws2.Range("C2")
    If MyName <> "" Then
        Arr = Split(MyName)
       
        Ws1.Cells.EntireRow.Hidden = False
        Ws1.UsedRange.Offset(1).Interior.Color = xlNone
        LRow = Ws1.Cells(Rows.Count, 1).End(3).Row
        LCol = Ws1.Cells(1, Columns.Count).End(xlToLeft).Column
        Ws1.Range(Ws1.Cells(2, 1), Ws1.Cells(LRow, 1)).EntireRow.Hidden = True
       
        For i = LBound(Arr) To UBound(Arr)
            x = WorksheetFunction.Trim(Arr(i))
            For j = 2 To LRow
                If WorksheetFunction.CountIf(Ws1.Cells(j, 1).EntireRow, x) > 0 Then Ws1.Cells(j, 1).EntireRow.Hidden = False
            Next j
        Next i
       
        For j = 2 To LRow
            For i = LBound(Arr) To UBound(Arr)
            x = WorksheetFunction.Trim(Arr(i))
                If WorksheetFunction.CountIf(Ws1.Cells(j, 1).EntireRow, x) > 0 Then k = k + 1
            Next i
            With Ws1.Range(Ws1.Cells(j, 1), Ws1.Cells(j, LCol)).Interior
                If k = 1 Then
                    .ThemeColor = xlThemeColorAccent4
                    .TintAndShade = 0.799981688894314
                ElseIf k > 1 Then
                    .ThemeColor = xlThemeColorAccent5
                    .TintAndShade = 0.799981688894314
                End If
            End With
            k = 0
        Next j
       
    End If
    Application.ScreenUpdating = True
End Sub

Leads to this result (using your example scenario)

cspengel.xlsm
	A	B	C	D
1	Name1	Name2	Name3	Name4
2	Adam	Bill	Helen	Steve
3	Bill	Chris	Adam	Tracy
4	Chris	David	Bill	Ursula
5	David	Eric	Chris	Vanessa
7	Adam	Gillian	Eric	Chris
11	Jackie	Adam	Irene	Gillian
12
Sheet1

I finally got around to trying to implement this code into my worksheet. Been a major headache as I clearly don't know what I am doing. As for the names portion of the code, I created a new sheet just to try it out. I can get it to work if the names are first names only. As soon as I add last names, it filters everything away instead of the selection I chose.

I've also been struggling to understand portions of the code to try and get it to the actual columns I want. (as what I am trying to filter is K:S

 

[kevin9999]

No worries
You’ll need to make a number of changes from the solution in post #6. Given the following data on sheet 1:

cspengel.xlsm
	K	L	M	N	O	P	Q	R	S
1	Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8	Name9
2	Jalen Hurts	Dalvin Cook	Cooper Kupp	A.J.Brown	Cooper Kupp	Kenneth Walker III	Dalvin Cook	Tony Pollard	Justin Jefferson
3	A.J.Brown	Jalen Hurts	Saquon Barkley	A.J.Brown	Cooper Kupp	Davante Adams	Derrick Henry	Saquon Barkley	Justin Jefferson
4	Dalvin Cook	Tony Pollard	Davante Adams	A.J.Brown	Cooper Kupp	Justin Jefferson	Davante Adams	Saquon Barkley	Davante Adams
5	Kenneth Walker III	Dalvin Cook	Davante Adams	DeVonta Smith	Cooper Kupp	Justin Jefferson	Kenneth Walker III	Kenneth Walker III	Tony Pollard
6	Kenneth Walker III	DeVonta Smith	Davante Adams	DeVonta Smith	Cooper Kupp	DeVonta Smith	Kenneth Walker III	Kenneth Walker III	Derrick Henry
7	Justin Jefferson	Derrick Henry	Jalen Hurts	Dalvin Cook	Cooper Kupp	Tony Pollard	Kenneth Walker III	Kenneth Walker III	DeVonta Smith
8	Davante Adams	Derrick Henry	Jalen Hurts	Dalvin Cook	Cooper Kupp	Tony Pollard	DeVonta Smith	Derrick Henry	DeVonta Smith
9	DeVonta Smith	Derrick Henry	Jalen Hurts	Dalvin Cook	Cooper Kupp	Saquon Barkley	Davante Adams	DeVonta Smith	Dalvin Cook
10	Saquon Barkley	DeVonta Smith	Davante Adams	DeVonta Smith	Cooper Kupp	A.J.Brown	Justin Jefferson	Saquon Barkley	A.J.Brown
11	Tony Pollard	Tony Pollard	Tony Pollard	Tony Pollard	Cooper Kupp	A.J.Brown	Justin Jefferson	Saquon Barkley	A.J.Brown
12
Sheet1

And this layout on sheet 2 (note the change to the formula used to get the unique list in cell E2 (change the last parameter 1000 to enough rows to capture all your data) – also, make the alignment format for cell E2 ‘wrap text’):

cspengel.xlsm
	C	D	E
1	Dropdown		Unique Names List
2	Derrick Henry; Jalen Hurts		A.J.Brown
3			Cooper Kupp
4			Dalvin Cook
5			Davante Adams
6			Derrick Henry
7			DeVonta Smith
8			Jalen Hurts
9			Justin Jefferson
10			Kenneth Walker III
11			Saquon Barkley
12			Tony Pollard
13
Sheet2

Cell Formulas
Range	Formula
E2:E12	E2	=SORT(UNIQUE(TOCOL(Sheet1!K2:S1000,3)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$E$2#

With this worksheet_change code (note the addition of the semi-colon) for sheet 2:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Address = "$C$2" 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
Exitsub:
    Application.EnableEvents = True
End Sub

And this amended sub:

Option Explicit
Sub cspengel_3()
    Application.ScreenUpdating = False
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Set Ws1 = Worksheets("Sheet1")
    Set Ws2 = Worksheets("Sheet2")
    Dim LRow As Long, LCol As Long, i As Long, j As Long, k As Long
   
    Dim MyName As String, x As String, Arr
    MyName = Ws2.Range("C2")
    If MyName <> "" Then
        Arr = Split(MyName, ";")
       
        Ws1.Cells.EntireRow.Hidden = False
        Ws1.UsedRange.Offset(1).Interior.Color = xlNone
        LRow = Ws1.Cells.Find("*", , xlFormulas, , 1, 2).Row
        LCol = Ws1.Cells(1, Columns.Count).End(xlToLeft).Column
        Ws1.Range(Ws1.Cells(2, 1), Ws1.Cells(LRow, 1)).EntireRow.Hidden = True
       
        For i = LBound(Arr) To UBound(Arr)
            x = WorksheetFunction.Trim(Arr(i))
            For j = 2 To LRow
                If WorksheetFunction.CountIf(Ws1.Cells(j, 1).EntireRow, x) > 0 Then Ws1.Cells(j, 1).EntireRow.Hidden = False
            Next j
        Next i
       
        For j = 2 To LRow
            For i = LBound(Arr) To UBound(Arr)
            x = WorksheetFunction.Trim(Arr(i))
                If WorksheetFunction.CountIf(Ws1.Cells(j, 1).EntireRow, x) > 0 Then k = k + 1
            Next i
            With Ws1.Range(Ws1.Cells(j, 11), Ws1.Cells(j, LCol)).Interior
                If k = 1 Then
                    .ThemeColor = xlThemeColorAccent4
                    .TintAndShade = 0.799981688894314
                ElseIf k > 1 Then
                    .ThemeColor = xlThemeColorAccent5
                    .TintAndShade = 0.799981688894314
                End If
            End With
            k = 0
        Next j
       
    End If
    Application.ScreenUpdating = True
End Sub

You’ll get this result:

cspengel.xlsm
	K	L	M	N	O	P	Q	R	S
1	Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8	Name9
2	Jalen Hurts	Dalvin Cook	Cooper Kupp	A.J.Brown	Cooper Kupp	Kenneth Walker III	Dalvin Cook	Tony Pollard	Justin Jefferson
3	A.J.Brown	Jalen Hurts	Saquon Barkley	A.J.Brown	Cooper Kupp	Davante Adams	Derrick Henry	Saquon Barkley	Justin Jefferson
6	Kenneth Walker III	DeVonta Smith	Davante Adams	DeVonta Smith	Cooper Kupp	DeVonta Smith	Kenneth Walker III	Kenneth Walker III	Derrick Henry
7	Justin Jefferson	Derrick Henry	Jalen Hurts	Dalvin Cook	Cooper Kupp	Tony Pollard	Kenneth Walker III	Kenneth Walker III	DeVonta Smith
8	Davante Adams	Derrick Henry	Jalen Hurts	Dalvin Cook	Cooper Kupp	Tony Pollard	DeVonta Smith	Derrick Henry	DeVonta Smith
9	DeVonta Smith	Derrick Henry	Jalen Hurts	Dalvin Cook	Cooper Kupp	Saquon Barkley	Davante Adams	DeVonta Smith	Dalvin Cook
12
Sheet1