Filtering in Excel

Mamoosh

New Member
Joined
Jul 29, 2010
Messages
4
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?


excelmamoosh.jpg
 

Excel Facts

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

MisterCrash

Active Member
Joined
Jan 27, 2003
Messages
279
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

New Member
Joined
Jul 29, 2010
Messages
4
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

Active Member
Joined
Jan 27, 2003
Messages
279
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,689
Messages
5,833,131
Members
430,195
Latest member
AnalystStoneSteps

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
Top