CODEWITHSUNDEEP
google-sheetsformula
Mark
Mark

Reputation: 18214

How can I add a column in Google Spreadsheet matrix?

I have two separate Google Spreadsheet documents, let's call them Tom and Jerry. They contain the same table but have different data in it. For example:

Tom:

SERIE            RATING
Friends          5
Suits            4
Ray Donavon      2

Jerry:

SERIE            RATING
Game of Thrones  5
Black Mirror     3
Breaking Bad     5

Now I like to combine them in a different Google Spreadsheet so I enter:

=QUERY(
    {
        QUERY(
            IMPORTRANGE("DOCUMENT_ID_TOM";"RANGE_DATA_NAME");"SELECT * WHERE Col1 <>''"
        );
        QUERY(
            IMPORTRANGE("DOCUMENT_ID_JERRY";"RANGE_DATA_NAME");"SELECT * WHERE Col1<>''"
        )
    };"SELECT *"
)

Now I have all the data:

Friends          5
Suits            4
Ray Donavon      2
Game of Thrones  5
Black Mirror     3
Breaking Bad     5

However what is lost in the query is that now I have no idea who rated what. So my preferred outcome would have been like this:

Tom    Friends          5
Tom    Suits            4
Tom    Ray Donavon      2
Jerry  Game of Thrones  5
Jerry  Black Mirror     3
Jerry  Breaking Bad     5

Now I don't want to add a useless column to the documents of Tom & Jerry containing simple the name copied in every row. There has to be another way so I can use it in a formula. So the question:

How can I add a column with the name in Google Spreadsheet matrix?

Upvotes: 0

Views: 99

Answers (1)

Karl_S
Karl_S

Reputation: 3564

How can I NOT dig for an answer when Tom and Jerry are involved?

Placing this in cell H1 of a sheet will cause column H to have the same values as column A, I as B, J as C AND has the added function of placing "Tom" (without the quotes) in column K:

={A1:C,ARRAYFORMULA(IF(ISBLANK(A1:A),,"Tom"))}

So we change that a bit for the Importrange function and get:

={IMPORTRANGE("DOCUMENT_ID_TOM", "sheet1!A1:C"),
   ARRAYFORMULA( 
    IF( 
      ISBLANK( 
          IMPORTRANGE("DOCUMENT_ID_TOM", "sheet1!A1:A")
      )
     ,,"Tom")
   )
 }

SO we should be able to use that for your formula to add Tom and Jerry to the imported ranges arrays and even simplify the formula a bit:

=QUERY(
  {
   {
    IMPORTRANGE("DOCUMENT_ID_TOM", "sheet1!A1:C"),
    ARRAYFORMULA(
     IF(
      ISBLANK(
       IMPORTRANGE("DOCUMENT_ID_TOM", "sheet1!A1:A")
      )
      ,,"Tom"
     )
    )
  };{
    IMPORTRANGE("DOCUMENT_ID_JERRY", "sheet1!A1:C"),
     ARRAYFORMULA(
      IF(
       ISBLANK(
        IMPORTRANGE("DOCUMENT_ID_JERRY", "sheet1!A1:A")
       )
      ,,"Jerry"
     )
    )
   }
 }
 , "Select * where Col2 <> ''"
)

Note that I am NOT using a named range to minimize the use of the various ranges but also because a named range does not grow. It goes to the last row in the sheet when the range was created.

The use of commas and semicolons in the {} determines if the values are added to the end of the array or create a new column. See the help on Arrays for more.

Upvotes: 1

Related Questions