11

I have a master spreadsheet that contains columns like so:

FirstName   LastName   Gender   etc, etc, etc

I need to import from that master sheet into another sheet that filters by one of the other columns. In the new sheet, I need name to be a single column, joining FirstName and LastName together.

Here is the current query I am using:

=query(importrange($SPREADSHEET_KEY, "Full Overview!A2:AH"), "select Col1, Col2, Col5 where Col8 contains 'Migrators'", 0)

I need to merge Col1 and Col2 from the master sheet into Col1 of the new sheet and put Col5 in Col2 of the new sheet.

3
  • Do you know how to concatenate two columns in Google Sheets? Aug 16, 2016 at 14:22
  • Can you concatenate inside the select? I didn't think you could.
    – Jonathanb
    Aug 18, 2016 at 9:28
  • Google Query select can concatenate but not in the way that you are expecting. See my answer. Aug 18, 2016 at 12:02

5 Answers 5

15

Short answer

QUERY select argument can't merge columns.

Explanation

The QUERY built-in function uses Google Visualization API Query Language. It doesn't include a concatenate operator. One alternative is to concatenate the data.

Examples

Assume that First Name and Last Name columns are columns A and B respectively.

Example 1

Add an auxiliary column to concatenate the desired columns in the source sheet and include this column in the IMPORTRANGE. Add one of the following formulas to an empty cell in the row 2:

  • =A2&" "&B2. Fill down as necessary.
  • =ARRAYFORMULA(A2:A&" "&B2:B) (Tip: Delete empty rows or use FILTER to only concatenate non-empty rows).

Example 2

Use several IMPORTRANGE, the concatenate operator & and arrays.

=ARRAYFORMULA(
  QUERY(
    {
      importrange($SPREADSHEET_KEY, "Full Overview!A2:A")&
      " "&
      importrange($SPREADSHEET_KEY, "Full Overview!B2:B"),
      importrange($SPREADSHEET_KEY, "Full Overview!C2:AH")
    },
    "select Col1, Col2, Col5 where Col8 contains 'Migrators'", 
    0
  )
) 
0
6

In certain cases you can use Google array literal representation to achieve a join.

=query({Sheet1!A:B, Sheet2!C:D}, "select Col1, Col3 where Col2 = Col4")

This works only when the number of rows in both tables is same.

3
  • But select Col1, Col3 outputs 2 cols. How to combine them into one col? (Col1 & " " & Col3)
    – Pacerier
    Nov 13, 2018 at 0:16
  • 1
    You can use JOIN statement on the result: =JOIN(" ", query({Sheet1!A:B, Sheet2!C:D}, "select Col1, Col3 where Col2 = Col4"))
    – rehan
    Nov 17, 2018 at 13:24
  • The use of Col1, Col2, Col3 by default is golden.. Where is it documented ?
    – cladelpino
    Mar 31, 2022 at 19:43
1

Another way to merge two columns from another google sheets is by using the BYROW function. The function will combine the rows of your query with the LAMBDA function you provide. Since you are combining text data you should use the JOIN function in the lambda. Example below:

=BYROW(query(IMPORTRANGE($SPREADSHEET_KEY, "Full Overview!A2:AH"),"select Col1, Col2 where Col8 contains 'Migrators'"),LAMBDA(row, JOIN(" ",row)))

You would need to perform another query in col2 of your destination spreadsheet to get the values from col5:

=query(IMPORTRANGE($SPREADSHEET_KEY, "Full Overview!A2:AH"),"select Col5 where Col8 contains 'Migrators'")
0

The easiest way (probably the only solution so far) is to add to the source table another column which contains the concatenated data.

0

Even better, combine the top two answers (of today)

The answer from Rubén introduces perhaps the most direct way to merge the columns by simply adding ArrayFormula() to a formula combining with &.

Then the answer from rehan reminds us that we can combine ranges inside query() easily with {}.

To avoid adding columns to the source set,

  • we can simply combine these two pieces, adding the concatenated ArrayFormula() column to the range in our query()

~
For example,

If columns A and B contain some other needed data for our query,
and then columns C and D need to be concatenated,
starting from row 1 and for all rows -

we could make a single query() statement as:

=query( 
        {
            $A$1:$B, 
            ArrayFormula( $C$1:$C & " " & $D$1:$D )
        }, 
        "select Col1, Col2, Col3"
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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