Filtering by one column based on values in another

[mightyr]

I all, hoping someone can help me with this one...

So I've got a table that contains a list of materials required for a project, essentially similar to this:

table.png

What I want to do is to display all material for lines with (for example) PVC pipe as one of the materials.

The result of this filter would be:

table1.png

So it lists all the materials for drawing numbers that contain "PVC pipe" as one of the materials.

Is there a simple way to do this without adding additional rows or using macros? On top of that - is there a way to also filter this way using a pivot table?

Any help is greatly appreciated!

[:) Sixthsense :)]

Hi mightyr,

Welcome to the forum

Please attach sample file with source data and pivot tables for giving exact solution

[mightyr]

Hi Sixthsense, thanks for the reply... unfortunately I can't post the exact spreadsheet I'm working on as it contains information for a construction project which I really shouldn't share... I can though post the simplified example I posted a screen shot of above if that works?

[:) Sixthsense :)]

We never request you to attach a confidential data, please just fill x,y,z of data to cover logic

[mightyr]

OK no worries, see attached simplified version. Thanks for your help!

[:) Sixthsense :)]

Check the attachment and confirm whether this helps

[mightyr]

Hey sixthsense, yep that works great... but as I said in my first post I can't add any columns to my spreadsheet, as my actual spreadsheet contains around 40 columns including hidden columns and adding a column will affect my macros etc....

Is there any other way to do this?

Again thanks for your help!

Edit: Btw - Your solution falls down if the 'material' you're looking for is not the first material listed for each 'drawing number'. Eg. with the same table if you wanted to complete the same operation with value say "Flange Type A'.

[:) Sixthsense :)]

Sorry I don't have any other suggestion

[mightyr]

Is it possible to filter a column by setting a range of cells as the criteria? If this was possible I could first filter the cells by 'Material' type, then copy the relevant visible "drawing number" cells to another sheet. Then, clear my current material filter and filter "drawing number" by the copied range. This could be done fairly easily with a macro, though I'm not sure it can be done without. Also I wouldn't know how to use that on a pivot table without copying visible cells to another sheet and creating a whole new pivot table.

[newdoverman]

You can use the filtering ability of the table.

Click the filer button on the Drawing Number and sort the drawings in numeric order. Then click again on the filter button for the Drawing Number and de-select All and select 1 and 3 and you will have duplicated the outcome in the picture that you posted.

[mightyr]

^The above manual selection methods works great if you have three drawing numbers with 5 materials each... but my actual spreadsheet has hundreds of drawings with up to 20 materials each. I want to filter to show all materials of all drawings that have "PVC" as one of the materials.

[newdoverman]

Filtering by materials and choosing for example PVC won't do what you want?

[newdoverman]

Filtering by materials and choosing for example PVC won't do what you want?

[mightyr]

I need to show all materials for drawings numbers that have PVC as one of the materials. If I filter by materials an choose PVC this will show the drawing numbers that have PVC as one of the materials but it obviously won't have the other materials for these drawings.

[newdoverman]

You can filter on as many of the materials as you like leaving out the ones that are not of interest. It isn't a case of choose one or choose all.

[mightyr]

As I said earlier I have hundreds of drawings and thousands of materials - filtering manually is not an option.

[mightyr]

I don't think you understand the question... All materials are of interest! It's the drawing numbers I want to filter... but I want to show all the materials of drawings that have a specific material (in this case PVC) as one of their (many) materials. If a drawing doesn't have PVC as one of it's materials, I want to hide that drawing number and all of its materials. If I filter materials and choose "PVC" it will show the drawing numbers I am interested in, but it will hide the other materials for those specific drawings that I am also needing to view. I could reset the filter then go through and manually select the drawing numbers... but if there are hundreds of drawings in this list this will be rather time consuming task.

[mightyr]

OK I figured a way to do it if anyone is interested... not the most elegant solution but it works and is easy.

Basically you need to make a dummy row. For this row make the value for 'Drawing number' anything eg. "Dummy" then make the value for material "PVC". Next filter materials so that only "PVC" is showing. Now go to the filter for 'drawing number' and uncheck "Dummy". Now go back to the filter for materials and click 'select all'. Done! Now the only way I can think to do a pivot table with this info is to copy the visible cells into a new sheet.