Is there a way to remove entries based on condition in =filter?

Question

I probably did not phrase my question accurately. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? I am pretty sure a mixture of query and arrayformula is needed but I can't figure it out.

Also - I am aware I can split it into multiple columns to achieve what I want but I am looking for a solution that requires only 1 formula.

Any suggestions would greatly help!

Answer 1

Try this formula:

=query({A1:B8},"select Col2 where Col1="&arrayformula(textjoin(" or Col1=",true,transpose(query({query({A1:B8}, "select Col1, count(Col2) where NOT (Col1 = 1 OR Col1=2 or Col1=3) and (Col1=4 or Col1=5 or Col1=6) group by Col1 label count(Col2) '' ")},"select Col1 where Col2 = 1"))))&"")

---

Sample

Note: I have added one extra row of data

---

Logic

• a simple query returning values in Column#2 based on a "where" clause: where Col1=4 or Col1=6
• the "where" clause is generated by "where Col1="&arrayformula(textjoin())
  • a nested query within textjoin provides the Column A values of 4 and 6:
    • query#1: returns Column A and the count of values in Column A based on a where clause relating to Column A (not equal to 1,2 or 3 AND equal to 4,5 or 6)
    • query#2: returns Column A where it has a value of 1 (that is, there is only one value attached to a Column A value)

Answer 2

Try this:

=UNIQUE(FILTER(BQ4:BQ10,ISERROR(VLOOKUP(1&BQ4:BQ10,BP4:BP10&BQ4:BQ10,1,FALSE))))

In plain English, this read as follows:

"Stick a 1 to the front of each element of the BQ range and try to find that string among any strings made up of the corresponding BP and BQ values for any row in the range. If found, filter out the BQ value. If not found (i.e., IFERROR) rule it in, and then return a unique listing of what's been ruled in."