Filtering in Excel

[Mamoosh]

Hi
I have an excel sheet (photo below) with column B showing "SafetyFeature", and column C, D, and E showing the relative building component. For example, in row 4, the safetyfeature 1 applies to building components 1 and 2.
What I need to see in the results is the safetyfeatures which apply to either building component 1 "or" 3. This means wherever item 1 or 3 is being showed in any of the cells across a safetyfeature, that safety feature is expected to be showed up using the filter.
If I use the regular filtering, it would show those safety features which apply to 1 or 3 in column C, "AND" 1 or 3 in column D, "AND" 1 or 3 in column E. This is when I need to show those safety features which apply to "EITHER" 1 or 3 in column C, "OR" 1 or 3 in column D, "OR" 1 or 3 in column E.
Any idea?

 

[MisterCrash]

If there is room on your spreadsheet, it would probably be easiest to make a new column that checks your condition, and then filter that one column. For example, in cell F3, use the formula:

=OR(OR(C3=1, C3=3), OR(D3=1, D3=3), OR(E3=1, E3=3))

Then, you can use filtering to find the TRUE values that show up in column F.

 

[Mamoosh]

Thanks MisterCrash for your quick response.
It works pretty good for me. But I'm working in a company which needs this function for some workers who need different combinations of the building components i.e. digits in the example above. It would be hard for them to write such a formula for every single output they are looking for. I prefer a more straight forward and easy to use function which can be used easily by a dummy! Maybe some sort of formula that "I" can embed in the file, and after that what the "user" does is just marking or unmarking the cells.

any idea?
Again, thanks for the tip.
http://www.mrexcel.com/forum/member.php?u=14927

 

[MisterCrash]

You can try setting up a table somewhere in the workbook that contains the codes that you want to filter for, then amend the formula so that it checks to see if any of the entries in that list are being used.

Let's say that cell H3 has the value 1 and cell H4 has the value 3. Then enter this formula in cell F3:

=(IF(COUNTIF($H$3:$H$4, C3)=0, 0, 1)+IF(COUNTIF($H$3:$H$4, D3)=0, 0, 1)+IF(COUNTIF($H$3:$H$4, E3)=0, 0, 1)>0)

This checks cells C3, D3, and E3 against the entries in cells H3 and H4. If any of C3, D3, or E3 are 1 or 3, the COUNTIF function will return a non-zero number, which makes the statement true. You can then change the values in cells H3 and H4 to test for other components.