0

I have a google sheet https://docs.google.com/spreadsheets/d/1mUV9DpVJHC2UbyqOG49wUIRj3EflTlB9etJQFssRLvo/ with a column "Floor", it contains the number and also character, I want to query the column and remove all empty cell, =unique(query(A:A,"SELECT A WHERE A IS NOT NULL ORDER BY A")) only the number be queried and all characters have been removed.

Can anyone advise how I can query all with unique and sort function?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Kelvin
  • 577
  • 7
  • 25

2 Answers2

1

I read the article from https://webapps.stackexchange.com/questions/101778/google-sheets-query-wont-display-cell-text-if-other-cells-have-numbers and come up a solution, hope this can help others.

=UNIQUE(ARRAYFORMULA(QUERY(TO_TEXT(A2:A), "SELECT Col1 WHERE Col1 IS NOT NULL ORDER BY Col1")))

Reason of using TO_TEXT() because mixed data types in a single column in Google Sheet, the majority data type determines the data type of the column for query purposes, so I convert all into text format. Ref: https://support.google.com/docs/answer/3094285?hl=en

UNIQUE is used to filter out all duplicated values

Regarding ARRAYFORMULA() function, I don't know why it is needed but QUERY() will return #VALUE! if missing the ARRAYFORMULA().

If someone can explain the use of ARRAYFORMULA() and Col1 reference, appreciate to answer.

Kelvin
  • 577
  • 7
  • 25
0

use the filter function instead. considering Column A has both numbers and characters. in B2, write: =filter(A2:A,isnumber(A2:A)) let me know if you need help!

Ken Adams
  • 70
  • 1
  • 1
  • 7
  • Thanks Ken, but I need the character cell also, LG is Lower Ground and UG is Upper Ground – Kelvin Jun 15 '19 at 03:04
  • sorry I misunderstood. Well in that case, the easy way would be as follows: =sort(unique(filter(A2:B,A2:A<>""))),1,true) Here, 1 represents the column number which should be the basis of sorting and true represents Ascending order. If you need further help, please share a working sheet. THanks – Ken Adams Jun 17 '19 at 05:30