Filter by roles - advanced filters

VirtualInsanity

New Member
Joined
May 23, 2012
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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! :)

CountryCityOfficeLevel/FloorOffice TypeNameMobileRole 1Training DateRole 2Training DateRole 3Training DateFire Extinguisher Training?Additional Notes
New ZealandNelson31 Test streetN/AMain OfficeJo bloggs021 123 456Emergency WardenS&W RepYes
New ZealandNelson31 Test streetN/AMain OfficeBen Lugg021 123 456Emergency WardenFirst Aid Officer15/09/2018Yes
New ZealandNelson31 Test streetN/AMain OfficeJason Body021 123 456Emergency WardenFirst Aid Officer15/10/2018Yes
New ZealandNelson31 Test streetN/AMain OfficeJune Knight021 123 456Emergency WardenFirst Aid Officer24/11/2018Yes
New ZealandNelson31 Test streetN/AMain OfficeEllie Harrison021 123 456First Aid Officer12/10/2018
New ZealandNelson31 Test streetN/AMain OfficeLuke Skywalker021 123 456First Aid Officer28/08/2018
New ZealandNelson31 Test streetN/AMain OfficeJustin Timberlake021 123 456First Aid Officer
New ZealandNelson89 Blogg DriveN/AMain OfficeJoseph van Burg021 123 456Emergency WardenFirst Aid Officer15/01/2019Yes
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hello VI,

Using the Advanced Filter may be your best option:-

VBA Code:
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.
 
Upvote 0
Hello VI,

Using the Advanced Filter may be your best option:-

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

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