Auto filter data by criteria

K

kets23

New Member
Joined
Feb 29, 2016
Messages
26
Hi

I would be grateful for some assistance.

I have a file that that contains relevant customers details that is continuously added through out. A Supervisor would then go in add in further details at a later time once available.

To make it easier for the relevant supervisors, I would like to do something where the supervisor enters their name in cell B3 (criteria) and this auto filters the whole table with their details grouped together (filters by the supervisor name which already appear in column B under the supervisor field entered in cell B3) so it shows all their data within the same table and not on another sheet) so they can enter the data that is missing easily without having to scroll all the way through the table.

I’m trying to make this as easy as possible and don’t want supervisors to manually filter as not all supervisors are confident using excel.

The spreadsheet currently has 50 rows of test data and this will be constantly added to. Have provided some of this below via a mini sheet

Would also like a clear filter button on so it reverts back to how it was.

I hope I’m making sense. If there’s a better way, do please let me know.

Thanks

Kets

Thanks in advanced

Test File.xlsx
ABCDEFGHIJKLMNOPQRS
1
2
3
4
5Employee NameSupervisorLine managerAreaPass/FailExtensionsPVSectionTypeProficienciesMedicines OARRecord of working Record of Communicationfeedback givenInterviewsInterview completedDate interview completedNotes
6Sam RogersTom BrownLoiuse WalshTPFailNo
7Tom SmithFred OwenBob PaulNHPassEXTPassNoYesFailPassFailNoNoYesYesYes23/09/2022
8Pam BradleyTom BrownBob PaulNHPassEXTFailNoNoFailPassFailNoYesYesYesYes23/09/2022
9Peter JonesFred OwenInca JinsSSEXT
10Kevin PriceJames JonesJack DanielsNHPassN/APassNoYesFailPassFailNoNoYesYesYes23/09/2022
11Luke PageJames JonesTom PrinceTPPassEXTFailNoNoPassPassFailYesNoYesYesNo
12Megan JepsonFred OwenLoiuse WalshNH
13Paul GivensOwen CloaksBob PaulNHPassN/APassNoYesFailPassPassNoYesYesYesYes23/09/2022
14Whitney JamesTom BrownBob PaulSSPassEXTFailNoNoPassFailFailNoNoYesYesYes23/09/2022
15Will ShatnerFred OwenInca JinsTPYes
16Bob JamesJames JonesJack DanielsJKPassEXTFailNoNoPassPassFailNoNoYesNoYes23/09/2022
17Jeff SnoodJames JonesTom PrinceSSFailYesFailPassNoNo
18Phil ThompsonFred OwenLoiuse WalshTP
19James KushJames JonesBob PaulTPPassEXTPassNoYesFailPassFailNoNoYesYesYes23/09/2022
20Sonia JulesJames JonesBob PaulSSEXTNo
21Katie MooresJames JonesInca JinsNHFailEXTFailYesNoPassFailPassNoNoNoYesYes23/09/2022
employee details
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I20Cell Value="No"textNO
F20Cell Value="N/A"textNO
F20Cell Value="EXT"textNO
H20,M20:Q20Cell Value="Yes"textNO
H20,M20:Q20Cell Value="No"textNO
H20:I20Cell Value="Pass"textNO
H20:I20Cell Value="Yes"textNO
G20,E20,J20:L20Cell Value="Pass"textNO
G20,E20,J20:L20Cell Value="Fail"textNO
G20,E20,J20:L20Cell Value="Fail"textNO
G20,E20,J20:L20Cell Value="Pass"textNO
I18Cell Value="No"textNO
F18Cell Value="N/A"textNO
F18Cell Value="EXT"textNO
H18,M18:Q18Cell Value="Yes"textNO
H18,M18:Q18Cell Value="No"textNO
H18:I18Cell Value="Pass"textNO
H18:I18Cell Value="Yes"textNO
G18,E18,J18:L18Cell Value="Pass"textNO
G18,E18,J18:L18Cell Value="Fail"textNO
G18,E18,J18:L18Cell Value="Fail"textNO
G18,E18,J18:L18Cell Value="Pass"textNO
I17Cell Value="No"textNO
F17Cell Value="N/A"textNO
F17Cell Value="EXT"textNO
H17,M17:Q17Cell Value="Yes"textNO
H17,M17:Q17Cell Value="No"textNO
H17:I17Cell Value="Pass"textNO
H17:I17Cell Value="Yes"textNO
G17,E17,J17:L17Cell Value="Pass"textNO
G17,E17,J17:L17Cell Value="Fail"textNO
G17,E17,J17:L17Cell Value="Fail"textNO
G17,E17,J17:L17Cell Value="Pass"textNO
I15Cell Value="No"textNO
F15Cell Value="N/A"textNO
F15Cell Value="EXT"textNO
H15,M15:Q15Cell Value="Yes"textNO
H15,M15:Q15Cell Value="No"textNO
H15:I15Cell Value="Pass"textNO
H15:I15Cell Value="Yes"textNO
G15,E15,J15:L15Cell Value="Pass"textNO
G15,E15,J15:L15Cell Value="Fail"textNO
G15,E15,J15:L15Cell Value="Fail"textNO
G15,E15,J15:L15Cell Value="Pass"textNO
I12Cell Value="No"textNO
F12Cell Value="N/A"textNO
F12Cell Value="EXT"textNO
H12,M12:Q12Cell Value="Yes"textNO
H12,M12:Q12Cell Value="No"textNO
H12:I12Cell Value="Pass"textNO
H12:I12Cell Value="Yes"textNO
G12,E12,J12:L12Cell Value="Pass"textNO
G12,E12,J12:L12Cell Value="Fail"textNO
G12,E12,J12:L12Cell Value="Fail"textNO
G12,E12,J12:L12Cell Value="Pass"textNO
I9Cell Value="No"textNO
F9Cell Value="N/A"textNO
F9Cell Value="EXT"textNO
H9,M9:Q9Cell Value="Yes"textNO
H9,M9:Q9Cell Value="No"textNO
H9:I9Cell Value="Pass"textNO
H9:I9Cell Value="Yes"textNO
G9,E9,J9:L9Cell Value="Pass"textNO
G9,E9,J9:L9Cell Value="Fail"textNO
G9,E9,J9:L9Cell Value="Fail"textNO
G9,E9,J9:L9Cell Value="Pass"textNO
I6Cell Value="No"textNO
F6Cell Value="N/A"textNO
F6Cell Value="EXT"textNO
H6,M6:Q6Cell Value="Yes"textNO
H6,M6:Q6Cell Value="No"textNO
H6:I6Cell Value="Pass"textNO
H6:I6Cell Value="Yes"textNO
G6,E6,J6:L6Cell Value="Pass"textNO
G6,E6,J6:L6Cell Value="Fail"textNO
G6,E6,J6:L6Cell Value="Fail"textNO
G6,E6,J6:L6Cell Value="Pass"textNO
Q21Cell Value="Yes"textNO
Q21Cell Value="No"textNO
M21Cell Value="Yes"textNO
M21Cell Value="No"textNO
I21Cell Value="No"textNO
H21Cell Value="Yes"textNO
H21Cell Value="No"textNO
F21Cell Value="N/A"textNO
F21Cell Value="EXT"textNO
N21:P21Cell Value="Yes"textNO
N21:P21Cell Value="No"textNO
H21:I21Cell Value="Pass"textNO
H21:I21Cell Value="Yes"textNO
J21:L21Cell Value="Pass"textNO
J21:L21Cell Value="Fail"textNO
J21:L21Cell Value="Fail"textNO
J21:L21Cell Value="Pass"textNO
E21Cell Value="Pass"textNO
E21Cell Value="Fail"textNO
E21Cell Value="Fail"textNO
E21Cell Value="Pass"textNO
G21Cell Value="Pass"textNO
G21Cell Value="Fail"textNO
G21Cell Value="Fail"textNO
G21Cell Value="Pass"textNO
Q19Cell Value="Yes"textNO
Q19Cell Value="No"textNO
M19Cell Value="Yes"textNO
M19Cell Value="No"textNO
I19Cell Value="No"textNO
H19Cell Value="Yes"textNO
H19Cell Value="No"textNO
F19Cell Value="N/A"textNO
F19Cell Value="EXT"textNO
N19:P19Cell Value="Yes"textNO
N19:P19Cell Value="No"textNO
H19:I19Cell Value="Pass"textNO
H19:I19Cell Value="Yes"textNO
J19:L19Cell Value="Pass"textNO
J19:L19Cell Value="Fail"textNO
J19:L19Cell Value="Fail"textNO
J19:L19Cell Value="Pass"textNO
E19Cell Value="Pass"textNO
E19Cell Value="Fail"textNO
E19Cell Value="Fail"textNO
E19Cell Value="Pass"textNO
G19Cell Value="Pass"textNO
G19Cell Value="Fail"textNO
G19Cell Value="Fail"textNO
G19Cell Value="Pass"textNO
Q16Cell Value="Yes"textNO
Q16Cell Value="No"textNO
M16Cell Value="Yes"textNO
M16Cell Value="No"textNO
I16Cell Value="No"textNO
H16Cell Value="Yes"textNO
H16Cell Value="No"textNO
F16Cell Value="N/A"textNO
F16Cell Value="EXT"textNO
N16:P16Cell Value="Yes"textNO
N16:P16Cell Value="No"textNO
H16:I16Cell Value="Pass"textNO
H16:I16Cell Value="Yes"textNO
J16:L16Cell Value="Pass"textNO
J16:L16Cell Value="Fail"textNO
J16:L16Cell Value="Fail"textNO
J16:L16Cell Value="Pass"textNO
E16Cell Value="Pass"textNO
E16Cell Value="Fail"textNO
E16Cell Value="Fail"textNO
E16Cell Value="Pass"textNO
G16Cell Value="Pass"textNO
G16Cell Value="Fail"textNO
G16Cell Value="Fail"textNO
G16Cell Value="Pass"textNO
Q14Cell Value="Yes"textNO
Q14Cell Value="No"textNO
M14Cell Value="Yes"textNO
M14Cell Value="No"textNO
I14Cell Value="No"textNO
H14Cell Value="Yes"textNO
H14Cell Value="No"textNO
F14Cell Value="N/A"textNO
F14Cell Value="EXT"textNO
N14:P14Cell Value="Yes"textNO
N14:P14Cell Value="No"textNO
H14:I14Cell Value="Pass"textNO
H14:I14Cell Value="Yes"textNO
J14:L14Cell Value="Pass"textNO
J14:L14Cell Value="Fail"textNO
J14:L14Cell Value="Fail"textNO
J14:L14Cell Value="Pass"textNO
E14Cell Value="Pass"textNO
E14Cell Value="Fail"textNO
E14Cell Value="Fail"textNO
E14Cell Value="Pass"textNO
G14Cell Value="Pass"textNO
G14Cell Value="Fail"textNO
G14Cell Value="Fail"textNO
G14Cell Value="Pass"textNO
Q13Cell Value="Yes"textNO
Q13Cell Value="No"textNO
M13Cell Value="Yes"textNO
M13Cell Value="No"textNO
I13Cell Value="No"textNO
H13Cell Value="Yes"textNO
H13Cell Value="No"textNO
F13Cell Value="N/A"textNO
F13Cell Value="EXT"textNO
N13:P13Cell Value="Yes"textNO
N13:P13Cell Value="No"textNO
H13:I13Cell Value="Pass"textNO
H13:I13Cell Value="Yes"textNO
J13:L13Cell Value="Pass"textNO
J13:L13Cell Value="Fail"textNO
J13:L13Cell Value="Fail"textNO
J13:L13Cell Value="Pass"textNO
E13Cell Value="Pass"textNO
E13Cell Value="Fail"textNO
E13Cell Value="Fail"textNO
E13Cell Value="Pass"textNO
G13Cell Value="Pass"textNO
G13Cell Value="Fail"textNO
G13Cell Value="Fail"textNO
G13Cell Value="Pass"textNO
Q11Cell Value="Yes"textNO
Q11Cell Value="No"textNO
M11Cell Value="Yes"textNO
M11Cell Value="No"textNO
I11Cell Value="No"textNO
H11Cell Value="Yes"textNO
H11Cell Value="No"textNO
F11Cell Value="N/A"textNO
F11Cell Value="EXT"textNO
N11:P11Cell Value="Yes"textNO
N11:P11Cell Value="No"textNO
H11:I11Cell Value="Pass"textNO
H11:I11Cell Value="Yes"textNO
J11:L11Cell Value="Pass"textNO
J11:L11Cell Value="Fail"textNO
J11:L11Cell Value="Fail"textNO
J11:L11Cell Value="Pass"textNO
E11Cell Value="Pass"textNO
E11Cell Value="Fail"textNO
E11Cell Value="Fail"textNO
E11Cell Value="Pass"textNO
G11Cell Value="Pass"textNO
G11Cell Value="Fail"textNO
G11Cell Value="Fail"textNO
G11Cell Value="Pass"textNO
Q10Cell Value="Yes"textNO
Q10Cell Value="No"textNO
M10Cell Value="Yes"textNO
M10Cell Value="No"textNO
I10Cell Value="No"textNO
H10Cell Value="Yes"textNO
H10Cell Value="No"textNO
F10Cell Value="N/A"textNO
F10Cell Value="EXT"textNO
N10:P10Cell Value="Yes"textNO
N10:P10Cell Value="No"textNO
H10:I10Cell Value="Pass"textNO
H10:I10Cell Value="Yes"textNO
J10:L10Cell Value="Pass"textNO
J10:L10Cell Value="Fail"textNO
J10:L10Cell Value="Fail"textNO
J10:L10Cell Value="Pass"textNO
E10Cell Value="Pass"textNO
E10Cell Value="Fail"textNO
E10Cell Value="Fail"textNO
E10Cell Value="Pass"textNO
G10Cell Value="Pass"textNO
G10Cell Value="Fail"textNO
G10Cell Value="Fail"textNO
G10Cell Value="Pass"textNO
Q8Cell Value="Yes"textNO
Q8Cell Value="No"textNO
M8Cell Value="Yes"textNO
M8Cell Value="No"textNO
I8Cell Value="No"textNO
H8Cell Value="Yes"textNO
H8Cell Value="No"textNO
F8Cell Value="N/A"textNO
F8Cell Value="EXT"textNO
N8:P8Cell Value="Yes"textNO
N8:P8Cell Value="No"textNO
H8:I8Cell Value="Pass"textNO
H8:I8Cell Value="Yes"textNO
J8:L8Cell Value="Pass"textNO
J8:L8Cell Value="Fail"textNO
J8:L8Cell Value="Fail"textNO
J8:L8Cell Value="Pass"textNO
E8Cell Value="Pass"textNO
E8Cell Value="Fail"textNO
E8Cell Value="Fail"textNO
E8Cell Value="Pass"textNO
G8Cell Value="Pass"textNO
G8Cell Value="Fail"textNO
G8Cell Value="Fail"textNO
G8Cell Value="Pass"textNO
Q7Cell Value="Yes"textNO
Q7Cell Value="No"textNO
M7Cell Value="Yes"textNO
M7Cell Value="No"textNO
I7Cell Value="No"textNO
H7Cell Value="Yes"textNO
H7Cell Value="No"textNO
F7Cell Value="N/A"textNO
F7Cell Value="EXT"textNO
N7:P7Cell Value="Yes"textNO
N7:P7Cell Value="No"textNO
H7:I7Cell Value="Pass"textNO
H7:I7Cell Value="Yes"textNO
J7:L7Cell Value="Pass"textNO
J7:L7Cell Value="Fail"textNO
J7:L7Cell Value="Fail"textNO
J7:L7Cell Value="Pass"textNO
E7Cell Value="Pass"textNO
E7Cell Value="Fail"textNO
E7Cell Value="Fail"textNO
E7Cell Value="Pass"textNO
G7Cell Value="Pass"textNO
G7Cell Value="Fail"textNO
G7Cell Value="Fail"textNO
G7Cell Value="Pass"textNO
Cells with Data Validation
CellAllowCriteria
E6:E189List=Sheet2!$A$1:$A$3
F6:F189ListN/A, EXT
G6:G189List=Sheet2!$A$1:$A$3
H6:I189ListYes, No
J6:L48List=Sheet2!$A$1:$A$3
M6:Q189ListYes, No
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Hi Kets,

I'm no friend of letting users type in something (typos...) so I would suggest to use a Data/Validation list for this in B3.

Code for a standard module:
VBA Code: 
Public Sub MrE_1223636_1615009()
' https://www.mrexcel.com/board/threads/auto-filter-data-by-criteria.1223636/
' Created: 20221202
' By:      HaHoBe

  Dim rngCell As Range
  Dim lngCounter As Long
  Dim objDic As Object
  Dim strSup As String
  
  Const cstrShName As String = "employee details"   'change name of sheet here to suit
 
  Set objDic = CreateObject("scripting.dictionary")
  With Worksheets(cstrShName)
    For Each rngCell In .Range("B6", .Range("B" & Rows.Count).End(xlUp))
      objDic.Item(rngCell.Value) = vbEmpty
    Next rngCell
    For lngCounter = 0 To objDic.Count - 1
      strSup = strSup & objDic.Keys()(lngCounter) & ","
    Next lngCounter
    With .Range("B3").Validation
      .Delete
      .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:=Left(strSup, Len(strSup) - 1)
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = "Choose name from list for filtering  clear to remove filtering"
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
    End With
  End With

End Sub

Code for ThisWorkbook:

VBA Code: 
Private Sub Workbook_Open()
  Call MrE_1223636_1615009
End Sub

Code behind the sheet with the data:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3")) Is Nothing Then
  If Target = vbNullString Then
    ActiveSheet.AutoFilterMode = False
  Else
    Rows("5:5").AutoFilter
    Rows("5:5").AutoFilter Field:=2, Criteria1:=Range("B3").Value
  End If
  Call MrE_1223636_1615009
End If
End Sub

Code will only work if macros are activated.

Ciao,
Holger
 
Upvote 0
HaHoBe said:
Hi Kets,

I'm no friend of letting users type in something (typos...) so I would suggest to use a Data/Validation list for this in B3.

Code for a standard module:
VBA Code: 
Public Sub MrE_1223636_1615009()
' https://www.mrexcel.com/board/threads/auto-filter-data-by-criteria.1223636/
' Created: 20221202
' By:      HaHoBe

  Dim rngCell As Range
  Dim lngCounter As Long
  Dim objDic As Object
  Dim strSup As String
 
  Const cstrShName As String = "employee details"   'change name of sheet here to suit
 
  Set objDic = CreateObject("scripting.dictionary")
  With Worksheets(cstrShName)
    For Each rngCell In .Range("B6", .Range("B" & Rows.Count).End(xlUp))
      objDic.Item(rngCell.Value) = vbEmpty
    Next rngCell
    For lngCounter = 0 To objDic.Count - 1
      strSup = strSup & objDic.Keys()(lngCounter) & ","
    Next lngCounter
    With .Range("B3").Validation
      .Delete
      .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:=Left(strSup, Len(strSup) - 1)
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = "Choose name from list for filtering  clear to remove filtering"
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
    End With
  End With

End Sub

Code for ThisWorkbook:

VBA Code: 
Private Sub Workbook_Open()
  Call MrE_1223636_1615009
End Sub

Code behind the sheet with the data:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3")) Is Nothing Then
  If Target = vbNullString Then
    ActiveSheet.AutoFilterMode = False
  Else
    Rows("5:5").AutoFilter
    Rows("5:5").AutoFilter Field:=2, Criteria1:=Range("B3").Value
  End If
  Call MrE_1223636_1615009
End If
End Sub

Code will only work if macros are activated.

Ciao,
Holger
Click to expand...
Thank you Ciao, will try this vba code.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
499
XLfriend777
XLfriend777
U
  • Question Question
Seeking Help with CELLS Property in VBA
2
Replies
13
Views
624
Alex Blakenburg
A
C
  • Solved
Countifs Number Of Times Team Play on each weekday
Replies
3
Views
284
CordingBags
C
W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
211
mumps
mumps
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
338
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top