Avatar of Doug Van
Doug Van
Flag 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.
SpreadsheetsMicrosoft ExcelMicrosoft OfficeGoogle WorkspaceGoogle

Avatar of undefined
Last Comment
Doug Van

8/22/2022 - Mon
Tom Farrar

This work if copied down..

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

If FALSE, then filter out.
Tom Farrar

Like this...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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. :)
Tom Farrar

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 Farrar

Never mind, it looks like you are not doing this in Excel..
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Doug Van

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

No. I'm using Google Sheets.
Doug Van

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')")
Doug Van

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question