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:
https://docs.google.com/spreadsheets/d/130fX-RhlHWxHHQpaKAGzrd7LPVFTM6gEdJqPQ4msTqY/edit#gid=0
Thank you.
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.
SpreadsheetsMicrosoft ExcelMicrosoft OfficeGoogle WorkspaceGoogle
Last Comment
Doug Van
If FALSE, then filter out.
Like this...
Example.PNG
Example.PNG
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
ASKER
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. :)
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..
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
>Never mind, it looks like you are not doing this in Excel..
No. I'm using Google Sheets.
No. I'm using Google Sheets.
ASKER
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
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
ASKER CERTIFIED SOLUTION
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
or
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
=AND(B5=ʺN/Aʺ, C5=ʺN/Aʺ)