Google sheets query on unique range-CodePudding

I have two columns Y and Z of some data:

Y   Z
G   U
P   U
G   U
G   U
G   P
G   P
P   U
R   U
R   P
C   P
C   U

I have got the unique values based on both columns like below:

 =UNIQUE(Y1:Z10)

I got of course:

A B <- columns
G U
P U
G P
U U
U P
C P
C U

Then I can get the second column like below:

 =QUERY(A1:B10;"select B")

So I got:

U
U
P
U
P
P
U

Great, I thought I have everything ready, just mix formulas:

=QUERY(UNIQUE(Y1:Z10);"select *") - > this is working - getting two columns, as expected. but when I try to select specific column - it's value error - no column "X".

The problem is with column name of course, but yeah, how to get it ?

CodePudding user response：

You can only use column letters (e.g., Z) if the QUERY is acting directly on the actual range. Since you've introduced UNIQUE, you've create a virtual array instead of a direct reference; and you must reference columns of virtual arrays within QUERY in "Colx" format, e.g.:

=QUERY(UNIQUE(Y1:Z10),"select Col2 WHERE Col2 Is Not Null")

BTW, you'll want the WHERE Col2 Is Not Null in there, since UNIQUE counts null as a unique value (i.e., every blank row processed by UNIQUE has a null value, which is returned with the results unless you further process it).