google-sheetssql-order-by
Using Google Sheets query, order by asc except one value that I would like to be last
This may be a total newbie question but I have done some level or research and I believe that Google Sheets Query function just can not handle this type of order by.
I have a table with Employee names on it.
| Employee | Status |
|---|---|
| Mike | Y |
| Chip | Y |
| Lucas | N |
| Jose | Y |
| Brian | Y |
| Lisa | Y |
| James | Y |
| Susie | Y |
| Zy | Y |
| Jack | Y |
| Other | O |
| April | N |
| Bob | N |
| Dilan | N |
| Summer | N |
| Kat | Y |
| Liz | N |
| Ellie | Y |
| Lisa | Y |
| Jan | N |
| Pam | N |
I've actually made this table a named range and called it "ACTEMP"
I have the following query: on another column:
=QUERY({ACTEMP}, "select Col1 where Col2 = 'Y' or Col2 ='O' order by Col1",1)
It sorta does the job and I get this:
| Employee |
|---|
| Brian |
| Chip |
| Jack |
| James |
| Jose |
| Lisa |
| Luis |
| Liz |
| Mike |
| Other |
| Summer |
| Susie |
| Zy |
But what I really need is this:
| Employee |
|---|
| Brian |
| Chip |
| Jack |
| James |
| Jose |
| Lisa |
| Luis |
| Liz |
| Mike |
| Summer |
| Susie |
| Zy |
| Other |
where 'Other' is always last.
Anyone know of a way of doing this?
Solution
You need two formula then stack them with VSTACK(). Try-
=VSTACK(
QUERY(A1:B7,"select A where B='Y' order by A",1),
QUERY(A1:B7,"select A where B='O'",0))
