Filter by roles - advanced filters

[VirtualInsanity]

Hi there,

I have a spreadsheet with people in various roles e.g. they may be a fire warden but they may also be a first aider or a safety rep or administrator. I'm trying to tidy it up so it just lists their name once and then has their various roles listed as role 1, role 2, role 3 etc. What I'm trying to figure out is how to put in an area where staff using the spreadsheet can search for various things like country, city, office, level, office type but with the key part being the Roles e.g. all staff who are listed as fire wardens - which could be listed in position 1, position 2 or position 3. Any help appreciated!

Country

City

Office

Level/Floor

Office Type

Name

Mobile

Role 1

Training Date

Role 2

Training Date

Role 3

Training Date

Fire Extinguisher Training?

Additional Notes

New Zealand

Nelson

31 Test street

N/A

Main Office

Jo bloggs

021 123 456

Emergency Warden

S&W Rep

Yes

New Zealand

Nelson

31 Test street

N/A

Main Office

Ben Lugg

021 123 456

Emergency Warden

First Aid Officer

15/09/2018

Yes

New Zealand

Nelson

31 Test street

N/A

Main Office

Jason Body

021 123 456

Emergency Warden

First Aid Officer

15/10/2018

Yes

New Zealand

Nelson

31 Test street

N/A

Main Office

June Knight

021 123 456

Emergency Warden

First Aid Officer

24/11/2018

Yes

New Zealand

Nelson

31 Test street

N/A

Main Office

Ellie Harrison

021 123 456

First Aid Officer

12/10/2018

New Zealand

Nelson

31 Test street

N/A

Main Office

Luke Skywalker

021 123 456

First Aid Officer

28/08/2018

New Zealand

Nelson

31 Test street

N/A

Main Office

Justin Timberlake

021 123 456

First Aid Officer

New Zealand

Nelson

89 Blogg Drive

N/A

Main Office

Joseph van Burg

021 123 456

Emergency Warden

First Aid Officer

15/01/2019

Yes

 

[vcoolio]

Hello VI,

Using the Advanced Filter may be your best option:-

Sub Test()

    If WorksheetFunction.CountA(Sheet2.Range("A2:O2")) = 0 Then Exit Sub

    Sheet2.Range("A3", Range("O" & Rows.Count).End(xlUp)).ClearContents
    Sheet1.Range("A1", Sheet1.Range("O" & Sheet1.Rows.Count).End(xlUp)).AdvancedFilter 2, Sheet2.[A1:O2], [A3]
    Sheet2.Columns.AutoFit
    
End Sub

Lets assume that you have all your data in Sheet1 (with no blank cells preferably) with headings in Row1 stretching out from Column A to Column O.

In Sheet2 Row1, copy/paste the headings from Sheet1. Leave Row2 blank, then paste the exact same headings into Row3. In Row3, create data validation drop down lists under any Row1 heading you wish.

Create a button and assign the above macro to it.

When you select a single criteria or a mix of criteria, a list of the relevant data will appear from Row4 on in Sheet2.

I've attached a sample workbook HERE showing you how it would work, add a criteria or a mix of criteria (note that I've only added drop down lists under Name, Role1 and Role2 in the sample) and then click on the 'GO" button.

I hope that this helps,
Cheerio,
vcoolio.

 

[VirtualInsanity]

Hello VI,

Using the Advanced Filter may be your best option:-

Sub Test()

    If WorksheetFunction.CountA(Sheet2.Range("A2:O2")) = 0 Then Exit Sub

    Sheet2.Range("A3", Range("O" & Rows.Count).End(xlUp)).ClearContents
    Sheet1.Range("A1", Sheet1.Range("O" & Sheet1.Rows.Count).End(xlUp)).AdvancedFilter 2, Sheet2.[A1:O2], [A3]
    Sheet2.Columns.AutoFit
   
End Sub

Lets assume that you have all your data in Sheet1 (with no blank cells preferably) with headings in Row1 stretching out from Column A to Column O.

In Sheet2 Row1, copy/paste the headings from Sheet1. Leave Row2 blank, then paste the exact same headings into Row3. In Row3, create data validation drop down lists under any Row1 heading you wish.

Create a button and assign the above macro to it.

When you select a single criteria or a mix of criteria, a list of the relevant data will appear from Row4 on in Sheet2.

I've attached a sample workbook HERE showing you how it would work, add a criteria or a mix of criteria (note that I've only added drop down lists under Name, Role1 and Role2 in the sample) and then click on the 'GO" button.

I hope that this helps,
Cheerio,
vcoolio.

Hi Vcoolio,

Thank you for this. Yes I looked at advanced filtering but what I'm finding with your example is that it's trying to match all the criteria to individual records i.e. if I put first aid officer as role 1 and also select first aid officer for role 2 it tries to find records showing first aid officer in both those roles thus giving me 0 results. I want it to check Role 1, Role 2 and Role 3 for first aid officer and return all the results it finds with that role listed anywhere on the spreadsheet.