We help IT Professionals succeed at work.

Filtering on IMPORTRANGE or filtering out content when two conditions are met

S Connelly
S Connelly asked
on
I am using the IMPORTRANGE function to pull in three columns from another sheet. The columns are:

Name, Option 1, Option 2

But I need a way to filter out any names that include N/A in both columns. In other words, filter out content when two conditions are met.

I prefer a formula based solution, rather then using Conditional filtering. The solution can use hidden columns if needed. Failing that conditional formatting is acceptable.

Your assistance is appreciated. :)

Here is the working sheet:
https://docs.google.com/spreadsheets/d/130fX-RhlHWxHHQpaKAGzrd7LPVFTM6gEdJqPQ4msTqY/edit#gid=0


Thank you.
Comment
Watch Question

View Solution Only

Tom FarrarConsultant

Commented:
This work if copied down..

=AND(B5=ʺN/Aʺ, C5=ʺN/Aʺ)
Tom FarrarConsultant

Commented:
If FALSE, then filter out.
Tom FarrarConsultant

Commented:
Like this...
Example.PNG
S ConnellyTechnical Writer

Author

Commented:
Thank you Tom.

When I stated that I prefer not to use "Conditional filtering", I really meant to say "Conditional Formatting" and "Filters" because they can't be  transferred to other sheets. They must be recreated individually.

That is why I prefer a purely formula based solution. :)
Tom FarrarConsultant

Commented:
Okay, not totally sure what that means, but if you really want just those where both N/A, and this is going to be a repeatable process, I suggest you think about taking the data into Power Query (Get and Transform) where the those records can be filtered out and the remaining rows output to a new sheet.  The advantage here could be once setup, it only requires refreshing of the output to update for new data.
Tom FarrarConsultant

Commented:
Never mind, it looks like you are not doing this in Excel..
S ConnellyTechnical Writer

Author

Commented:
>Never mind, it looks like you are not doing this in Excel..

No. I'm using Google Sheets.
S ConnellyTechnical Writer

Author

Commented:
Thanks for your help Tom.

After digging through documentation and a lot of trial and errors (emphasis on errors - LOL), I am happy to update with a solution. Hopefully, this will help out someone else someday.

=QUERY(IMPORTRANGE("import sheet URL","sheet tab!A3:D10"),"where not (Col2='N/A' and Col3='N/A')")
Technical Writer
Commented:
Solution above ^.