11

I am trying to piece together a query that does a few things. One, I want it to list out all the names in a column, and two, I only want it to list out the name from that column if it doesn't exist within an array of columns. 

=QUERY(QUERY(Breakdown!$A$2:$B), "select Col1 where Col1 != '' and Col2 = 'Warrior' order by Col1 Asc")

I got as far as this, which displays all of the names in the column as I want it to, but when I start adding in 'not in' type parameters, I break it every which way. How do I check that Col1 doesn't exist in the range ='Raid Comp'!A2:Q10?

Here is the spreadsheet: https://docs.google.com/spreadsheets/d/1X0GiOCAAve1CR4A3JG2Ybf-daMvrrhAsZF5V3XEdn4E/edit?usp=sharing

What I am tryin to do is once a name is entered within the colored areas, if name entered exists in the list below the colored area, the name is removed from the list.

Example:

enter image description here enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
addohm
  • 2,248
  • 3
  • 14
  • 40

1 Answers1

10

try regex in query:

=QUERY({Breakdown!$A$2:$B}, 
 "select Col1 
  where Col2 = 'Warrior' 
    and not Col1 matches '"&TEXTJOIN("|", 1, 'Raid Comp'!A2:Q10)&"'  
  order by Col1 asc")
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    I was able to make it work with `=QUERY(QUERY(Breakdown!$A$2:$B), "select Col1 where Col1 != '' and Col2 = 'Warrior' and not Col1 matches '"&TEXTJOIN("|", 1, 'Raid Comp'!A2:Q10)&"'order by Col1 Asc")` If you edit your answer, I will mark it as the answer. Thanks! – addohm Mar 31 '20 at 11:38
  • I'm saying your initial formula is broken (Look at test sheet M14) and I've given you the formula I used to make it work (though it may not be perfect). I'll mark your answer as an answer when its corrected. – addohm Apr 01 '20 at 00:26