0

enter image description here

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!

4
  • Hi and welcome. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? Yes, but then doesn't "b" also fulfil the '<>1' condition? May I respectfully suggest that you ignore FILTER (and QUERY and ARRAYFORMULA for a moment) and describe exactly what is the problem that you are trying to solve, and explain why "b" is the correct outcome instead of "a" and "b"?
    – Tedinoz
    Dec 29, 2021 at 9:33
  • Hi! Basically, I would like to get the unique entries in the second column where the first column does not contain 1,2 or 3 and only contains 4 or 5.
    – helpmepls
    Dec 29, 2021 at 16:27
  • OK. Would you please edit your question to say that. BUT... you say the result ="b", but surely the unique values that have a value of 4 or 5 in Col1 include "a" and as well as "b". Would you please explain (and demonstrate) why your expected result does not include "a".
    – Tedinoz
    Dec 29, 2021 at 23:28
  • Regarding your expected result, you used the term unique entries in the second column however is it possible that you don't mean "unique" in the normal sense but rather you mean entries where there is only one item. For example, Col A=4, there is a single value but Col A=5, there are two items.
    – Tedinoz
    Dec 29, 2021 at 23:49

2 Answers 2

0

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

snapshot


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)
0

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."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.