Excel help needed! Filtering data and nice dropdowns

[metalangel]

Excel wizards, please help!
 
I’ve been given a list of vendors. Each covers one or more of our three regions: West, Central and East.
 
What I’m trying to do is have a nice little drop down box at the top spreadsheet where I can tick whether I want some or all of the vendors to show based on their region – complicated because some cover more than one region.
I don’t have a clue how to do this, and once I’ve figured it out I’ll need to do it again to narrow the results even further based on which of three departments here they work with.
 
Thank you!
____________________
Previous: 2002 Honda CB500 (sold), 2007 Suzuki SV650SK6 (crashed), 2005 Yamaha FZ6 Fazer (sold). Currently bikeless
"A faired bike will get you 10x more clunge than a unfaired one." -Marlboro Matt

[Aff]

Can you send an example of the Spreadsheet?

If not it depends how the multiple locations are set up.

If they are in separate rows then its easy.

Just make a pivot table with the vendor as the column and the different location rows as separate report filters.
____________________
Current Bikes:Honda 929RR Fireblade, Honda CD200 Benly (Project), Stomp Z2 140
Electric Bike Project

[metalangel]

At the moment it's divided by columns, vendor name, address (this isn't something I am going to let them search by) and then region.

I had the idea of making a separate column for west, central and east and putting an X in if that region is covered by that vendor, because I don't have a clue what I'm doing.
____________________
Previous: 2002 Honda CB500 (sold), 2007 Suzuki SV650SK6 (crashed), 2005 Yamaha FZ6 Fazer (sold). Currently bikeless
"A faired bike will get you 10x more clunge than a unfaired one." -Marlboro Matt

[metalangel]

Made a Pivot Table, seems halfway there - how would you set it so you can have multiple words in the Regions column (for example, Central and West) but be able to pick either just one or both?
____________________
Previous: 2002 Honda CB500 (sold), 2007 Suzuki SV650SK6 (crashed), 2005 Yamaha FZ6 Fazer (sold). Currently bikeless
"A faired bike will get you 10x more clunge than a unfaired one." -Marlboro Matt

[The Shaggy D.A.]

https://www.wikihow.com/Use-AutoFilter-in-MS-Excel
____________________
Chances are quite high you are not in my Monkeysphere, and I don't care about you. Don't take it personally.
Currently : Royal Enfield 350 Meteor
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom > Royal Enfield Classic 500 Despatch Limited Edition (28 of 200) & CB Two-Fifty Nighthawk > ER5

[Aff]

[metalangel]

[The Shaggy D.A.]

Have you tried the auto filter yet?
____________________
Chances are quite high you are not in my Monkeysphere, and I don't care about you. Don't take it personally.
Currently : Royal Enfield 350 Meteor
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom > Royal Enfield Classic 500 Despatch Limited Edition (28 of 200) & CB Two-Fifty Nighthawk > ER5

[metalangel]

[The Shaggy D.A.]

Can't you click on "custom", then select "contains" and set it to "West"?

What version of Excel are you using?
____________________
Chances are quite high you are not in my Monkeysphere, and I don't care about you. Don't take it personally.
Currently : Royal Enfield 350 Meteor
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom > Royal Enfield Classic 500 Despatch Limited Edition (28 of 200) & CB Two-Fifty Nighthawk > ER5

[The Shaggy D.A.]

Is this close to what you're after?
____________________
Chances are quite high you are not in my Monkeysphere, and I don't care about you. Don't take it personally.
Currently : Royal Enfield 350 Meteor
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom > Royal Enfield Classic 500 Despatch Limited Edition (28 of 200) & CB Two-Fifty Nighthawk > ER5

[metalangel]

I can do that, but that doesn't let me add a specific "Central" tick box to the filter nor does it let me remove the tick boxes that combine values.

Excel 2010. Section of the file (with company names changed, obv) is here:

https://skydrive.live.com/redir?resid=705BF3A6F7ED7E60!167&authkey=!AJYJjTqKxCVqiJU&ithint=file%2c.xlsx
____________________
Previous: 2002 Honda CB500 (sold), 2007 Suzuki SV650SK6 (crashed), 2005 Yamaha FZ6 Fazer (sold). Currently bikeless
"A faired bike will get you 10x more clunge than a unfaired one." -Marlboro Matt

[metalangel]

[el_oso]

open vba and define your own filters.

you could also have two rows for a particular company
____________________
Duke 390
Previous: '05 XR125L | '96 XJ600S Diversion |'05 Suzuki GSXR1000 | '05 Honda CBR125-R | '97 YZF 600R Thundercat | '11 Honda CBR250
Car: Jeep Wrangler 4.0L

[D O G]

[metalangel]

What the fuck are you on about?
____________________
Previous: 2002 Honda CB500 (sold), 2007 Suzuki SV650SK6 (crashed), 2005 Yamaha FZ6 Fazer (sold). Currently bikeless
"A faired bike will get you 10x more clunge than a unfaired one." -Marlboro Matt

[D O G]

I'm trying to help you do your job better, rather than fucking around because you lack the balls to go back to the departments now that you actually know the requirement.

[metalangel]

If you saw the extent information I'd already been provided with and the full details of what I've been asked to do with it, rather than second guessing, you'd see what a load of nonsense you'd just talked.

Probably the most crucial thing is that all of the vendors came on lists specifically saying what regions they service. I have everything I need already to get this done as requested, and just wanted to make it a bit nicer looking in Excel, hence that being the only thing I asked for help on.
____________________
Previous: 2002 Honda CB500 (sold), 2007 Suzuki SV650SK6 (crashed), 2005 Yamaha FZ6 Fazer (sold). Currently bikeless
"A faired bike will get you 10x more clunge than a unfaired one." -Marlboro Matt

[D O G]

Three columns to the right of the data.

One titled 'West', one titled 'Central', one titled 'East'.

Use logic formula to populate these columns with 'Yes' if that vendor services that area (or you can do it manually for the ambiguous ones).

Some vendors will have 'Yes' in only one, some in two, and some in all three.

Then whack it into the pivot. Use the region columns as filter fields on the pivot. To select West vendors only, set the west column to 'Yes', and have the other two unfiltered. To find those which service west and east but not Central, set West column to 'Yes', Central to '(blanks)' and East column to 'Yes'.

By adding further columns to the right, you can use those to populate which departments they work with - which should be a piece of piss to populate as you will be able to use the list the department sent you as a data source for a logic formula using a vlookup to give a 'Yes/no' determination for any vendor. Probably take you an hour or two at most, including time for ironing out any issues with naming consistency.

[el_oso]