12

I have a table:

 A     |   B      |     C
BEN    |  MOSKOW  |    YES
ANTON  |  IRKUTSK |     NO
VIKTOR |  PARIS   |    YES
BEN    |  PARIS   |    YES
ANTON  |  TORONTO |     NO
DON    | TORONTO  |    YES
ANNA   | IRKUTSK  |    YES
BEN    |  MOSKOW  |     NO

and tried a formula:

=UNIQUE(query(A:C; "Select A,B,C where A contains 'BEN' OR A contains 'ANTON' order by A"))

and an arrayformula:

=arrayformula(UNIQUE(query(A:C; "Select A,B,C where A contains 'BEN' OR A contains 'ANTON' order by A")))

but the results are in columns:

ANTON  |  IRKUTSK |     NO
ANTON  |  TORONTO |     NO
BEN    |  MOSKOW  |    YES
BEN    |  PARIS   |    YES
BEN    |  MOSKOW  |     NO

whereas I need results in only one cell per row, like so:

ANTON IRKUTSK NO
ANTON TORONTO NO
BEN MOSCOW YES
BEN PARIS YES
BEN MOSKOW NO
Community
  • 1
  • 1
Антон Смольный
  • 327
  • 1
  • 2
  • 7

2 Answers2

15

There is no concat option in Google Sheets query.

Workaround, suggested by JPV may fit you: https://stackoverflow.com/a/29799384/5372400

use formula:

=ARRAYFORMULA( SUBSTITUTE( trim(transpose(query(transpose(_your_query_here_),,COLUMNS(_your_query_here_))))," "," → ") )

Change " → " to space " " to concat the result with space.

More info about SQL in Sheets:

https://developers.google.com/chart/interactive/docs/querylanguage

Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
7

Use Transpose twice.

=transpose(query(transpose(_you query_);;COLUMNS(_you query_)))

For an example from my question:

=transpose(query(transpose(UNIQUE(query(A:C; "select * where A contains 'BEN' OR A contains 'ANTON' order by A")));;COLUMNS(UNIQUE(query(A:C; "select * where A contains 'BEN' OR A contains 'ANTON' order by A")))))

Result:

ANTON IRKUTSK NO
ANTON TORONTO NO
BEN MOSCOW YES
BEN PARIS YES
BEN MOSKOW NO
Антон Смольный
  • 327
  • 1
  • 2
  • 7