So I've been struggling with this one query:
Column A
--
usa-persona
ind-personb
kor-personc
jpn-persond
Column B
--
usa-persona
ind-personb
ind-personb2
ind-personb3
Desired Output
--
ind-personb2
ind-personb3
- Search through column B,
- Return ones that have the string 'ind',
- But they should not be in Column A.
This is what I have, which doesn't seem to do the last part (checking to see if they are in Column A). QUERY(A:B, "SELECT B where B contains 'ind' and not B matches '"&A:A&"'")) How do I go about this?
CodePudding user response：
Use filter(), like this:
=filter( B2:B, search("ind", B2:B), isna(match(B2:B, A2:A, 0)) )
CodePudding user response：
use:
=FILTER(B1:B; NOT(COUNTIF(A1:A; B1:B)))
