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?


 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,927
Messages
5,514,211
Members
408,990
Latest member
fresse68

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top