Filtering Question - probably very basic...

[leemim]

Hi all, having a dilemma in work so I've created a dummy replica for the sake of simplicity - the answer should apply to my RL scenario.

In the example shown below, ABC Cleaning Company have a list of properties that they clean, and whether or not that property pays for cleaning in certain rooms (Yes = they pay, so the room gets cleaned. No = they don't pay, room isn't cleaned).

For some properties, ABC Cleaning Company outsources the cleaning of certain rooms, which is signified by "Outsourced" next to "Yes" in the provision column.

In my RL scenario, I have 3000+ "properties" and about 50+ "rooms" - what I want to do is filter the spreadsheet so that it shows ONLY properties where ABC Cleaning Company have NO responsibility WHATSOEVER. That means the property should either have all 'No' in the rooms, or 'Yes - Outsourced' (treating Outsourced as a 'No').

What is the best way of achieving this?

Thank you so much for your help in advance!

 

[SQUIDD]

ok

so you need to look to see if the cell contains "outsourced" rather than equals "outsourced"
Also, when you get a N/a, im not sure how you are getting this error? I think i will need more info on the exact data within the cells so i can replicate it.

The formula below will check to see if the cell contains the word outsourced.

=IF(AND(OR(C4="NO",ISNUMBER(SEARCH("outsourced",D4))),OR(E4="NO",ISNUMBER(SEARCH("outsourced",F4))),OR(G4="NO",ISNUMBER(SEARCH("outsourced",H4))),OR(I4="NO",ISNUMBER(SEARCH("outsourced",J4))),OR(K4="NO",ISNUMBER(SEARCH("outsourced",L4))),OR(M4="NO",ISNUMBER(SEARCH("outsourced",N4)))),"no  responsibility","responsible")

out of intrest, when you get N/a, is there anything in particular you want the cell to display.

let me know, dave