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]

would you be willing to have a helper column with a formula?

Then you could simply filter the column?

a formual similar to this, i know its not quite right as we need to add the outsourced part but no point if your not happy to use a helper column

=IF(AND(C4="no",E4="no",G4="no",I4="no",K4="no",M4="no"),"no responsibility","responsible")

dave

 

[leemim]

I'm not sure what a helper column is, but willing to do anything, so long as it is only shows properties where they're all either 'No' or 'Outsourced'!

How do I go about adding a helper column, sorry?

 

[SQUIDD]

Hi leemim

welcome to MR excel BTW.

sorry, what is meant by a helper column .
It simply means using an additional blank column within your worksheet to enter a formula.
So in your example posted i would say column O. We would write a formula within cell O4

=IF(AND(C4="no",E4="no",G4="no",I4="no",K4="no",M4="no"),"no responsibility","responsible")

we would then copy the formula down the column to the end of your data.
Then we can filter column O for the wording "no responsibility"

Does this make sense now?

Dave

 

[leemim]

Hi Dave - and thanks!

I've applied this formula, but when I apply it down the rows, it is showing all properties as 'responsible'. But it should not be the case for '50 Fake Road' as that contains only 'No' and 'Outsourced' in all the rooms...

Is there a way to also tell the formula to check for 'Outsourced'?

 

[SQUIDD]

try this

hope it works for you

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

dave

 

[leemim]

Dave, it worked - thank you so, so much - you are a life saver!! Truly grateful!

 

[SQUIDD]

Glat to help and glad it worked for you.

Thanks for the reply

dave

 

[leemim]

Hi,

I have applied this formula to my RL scenario and I am receiving some #N/A results. I believe the reason for this is because the 'Provision' column contains more than just 'Outsourced' (it also contains Other, In-House, Mixed etc.).

How can I tell your suggested formula to ONLY look for 'No' and the 'Outsourced' (in provision column) and ignore all other possible entries inside these columns?

This is what I am currently using:

=IF(AND(OR(u2="NO",v2="Outsourced"),OR(ae2="NO",af2="Outsourced"),OR(ao2="NO",ap2="Outsourced"),OR(BA2="NO",bb2="Outsourced"),OR(bk2="NO",bl2="Outsourced"),OR(bu2="NO",bv2="Outsourced"),OR(ce2="NO",cf2="Outsourced"),OR(co2="NO",cp2="Outsourced"),OR(cy2="NO",cz2="Outsourced"),OR(di2="NO",dj2="Outsourced"),OR(ds2="NO",dt2="Outsourced"),OR(eb2="NO",ec2="Outsourced"),OR(ek2="NO",el2="Outsourced"),OR(et2="NO",es2="Outsourced"),OR(fc2="NO",fd2="Outsourced")),"None","Yes")

 

[leemim]

I should mention that I am halfway there as it does return the properties where services are provided. But there are no 'Nones', just 'N/As' (again because the Provision column has multiple entries other than just 'Outsourced').