Searching multiple columns and hide rows

[Blanchetdb]

I presently working with a google docs form (can not change that form as it is used by multiple departments). The data is exported to an excel form that gives me the following:

First Name	Last Name	Language	Province (choice 1)	Province (Choice 2)	City (Choice 1)	City (Choice 2)	Specialization 1	Specialization 2	Specialization 3
Jane	Doe	French	New Brunswick	Nova Scotia	Halifax	Moncton	Environment	Science
John	Smith	English	Quebec	Ontario	Montreal	Toronto	Science	Administration	Environment
Fred	Timmons	English	Manitoba	New Brunswick	Winnipeg	Moncton	Science

I need a way to quickly search throughout the document (it presently has 600 rows) and there are more options than shown but this is a sample.

I would like to able to use a drop menu for Province, City and Specialization in order to choose what I am seeking.... what does not match all requirements, the row hides - leaving only (visible) the rows that meet what has been chosen.

example:
Province: Ontario / City: Toronto / Specialization: Science
RESULT:

John

Smith

English

Quebec

Ontario

Montreal

Toronto

Science

Administration

Environment

All other "no match" are not visible .... If there are no match ... a message box appears stating " No results found"

 

[mumps]

You would need to have a list of cities, provinces and specializations (perhaps on a different sheet) to populate the drop down menus. Where do you want the drop down menus?

 

[Blanchetdb]

You would need to have a list of cities, provinces and specializations (perhaps on a different sheet) to populate the drop down menus. Where do you want the drop down menus?

I do have a separate sheet that have all the dropdown options. Ultimately, it would be great if the drop down menus would be on a separate sheet but trigger the hide/show on the main raw data sheet

 

[mumps]

What are the names of the Data sheet and the sheet with the drop down options? In which columns are the cities, provinces and specializations?

 

[Blanchetdb]

What are the names of the Data sheet and the sheet with the drop down options? In which columns are the cities, provinces and specializations?

Raw Data sheet is called - Recruitment
Drop options list is called - List
Sheet that would contain the dropdown boxes - Referral

I gave you an abbreviated version of the data sheet that I get as the data goes from column A to CN.... there are 3 columns for Provinces, 3 for Cities, 3 for specializations ... I can adapt what you are able to provide to fit the proper columns.

thank you for your time on this

 

[mumps]

Click here to download a sample file. In the Referral sheet, first select a province, then a city and lastly a Specialization. The macro runs automatically when you select a Specialization so it is important that it be selected last. The code is located in the worksheet code module. To see the code, right click the tab name for your Referral sheet and click 'View Code'. Close the code window to return to your sheet. This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C2" Then Exit Sub
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, v As Variant, i As Long, fnd As Range
    Set srcWS = Sheets("Recruitment")
    v = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp))
    For i = UBound(v) To LBound(v) Step -1
        Set fnd = srcWS.Rows(i + 1).Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        If fnd Is Nothing Then
            srcWS.Rows(i + 1).Hidden = True
        Else
            Set fnd = srcWS.Rows(i + 1).Find(Target.Offset(, -1).Value, LookIn:=xlValues, lookat:=xlWhole)
            If fnd Is Nothing Then
                srcWS.Rows(i + 1).Hidden = True
            Else
                Set fnd = srcWS.Rows(i + 1).Find(Target.Offset(, -2).Value, LookIn:=xlValues, lookat:=xlWhole)
                If fnd Is Nothing Then
                    srcWS.Rows(i + 1).Hidden = True
                End If
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[mumps]

I forgot to mention that you can change the lists and the drop downs will adjust automatically.

 

[Blanchetdb]

Click here to download a sample file. In the Referral sheet, first select a province, then a city and lastly a Specialization. The macro runs automatically when you select a Specialization so it is important that it be selected last. The code is located in the worksheet code module. To see the code, right click the tab name for your Referral sheet and click 'View Code'. Close the code window to return to your sheet. This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C2" Then Exit Sub
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, v As Variant, i As Long, fnd As Range
    Set srcWS = Sheets("Recruitment")
    v = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp))
    For i = UBound(v) To LBound(v) Step -1
        Set fnd = srcWS.Rows(i + 1).Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        If fnd Is Nothing Then
            srcWS.Rows(i + 1).Hidden = True
        Else
            Set fnd = srcWS.Rows(i + 1).Find(Target.Offset(, -1).Value, LookIn:=xlValues, lookat:=xlWhole)
            If fnd Is Nothing Then
                srcWS.Rows(i + 1).Hidden = True
            Else
                Set fnd = srcWS.Rows(i + 1).Find(Target.Offset(, -2).Value, LookIn:=xlValues, lookat:=xlWhole)
                If fnd Is Nothing Then
                    srcWS.Rows(i + 1).Hidden = True
                End If
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

thank you

this works great .... I would really like to understand the functioning of the macro. Could you possibly put comments prior to each step so that I know what does what .. example: 'searches the province

thanks

 

[mumps]

Here is the code with comments:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C2" Then Exit Sub
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, v As Variant, i As Long, fnd As Range
    Set srcWS = Sheets("Recruitment")
    v = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)) 'the v variable is an array that represents the values in column A of Recruitment
    For i = UBound(v) To LBound(v) Step -1 'loops through the array starting at the bottom of column A working up
        Set fnd = srcWS.Rows(i + 1).Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole) 'searches for the Specialization in row i + 1 of Recruitment
        If fnd Is Nothing Then 'if the Specialization is not found, row i + 1  is hidden by the next line of code
            srcWS.Rows(i + 1).Hidden = True
        Else 'if the Specialization is found .....
            Set fnd = srcWS.Rows(i + 1).Find(Target.Offset(, -1).Value, LookIn:=xlValues, lookat:=xlWhole) 'it searches for the city in the same row
            If fnd Is Nothing Then 'if the city is not found, row i + 1 is hidden by the next line of code
                srcWS.Rows(i + 1).Hidden = True
            Else 'if the city is found .......
                Set fnd = srcWS.Rows(i + 1).Find(Target.Offset(, -2).Value, LookIn:=xlValues, lookat:=xlWhole) 'it searches for the province in the same row
                If fnd Is Nothing Then 'if the province is not found, row i + 1 is hidden by the next line of code
                    srcWS.Rows(i + 1).Hidden = True
                End If
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Basically, if any one of the province, city or specialization is missing in any row, that row is hidden.