Link to home
Start Free TrialLog in
Avatar of Doug Van
Doug VanFlag for Canada

asked on

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

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:

Thank you.
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

This work if copied down..

=AND(B5=ʺN/Aʺ, C5=ʺN/Aʺ)
If FALSE, then filter out.
Like this...
Avatar of Doug Van


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. :)
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.
Never mind, it looks like you are not doing this in Excel..
>Never mind, it looks like you are not doing this in Excel..

No. I'm using Google Sheets.
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')")
Avatar of Doug Van
Doug Van
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial