CODEWITHSUNDEEP
google-sheetsformulasgoogle-query-language
Marek L.
Marek L.

Reputation: 323

Add column with cell values to tables merged using Query

I use this Query formula in Google Spreadsheet to merge tables from many source sheets into one table:

=query({Data1!A4:B;Data2!A4:B;Data3!A4:B}; "select * where Col1 is not null")

To distinguish original tables in the merged table, I need to add new column to the merged table with their identification. The indentification string is in cells on every source sheet. How to do it?

This is example spreadsheet I prepared for tests. Copy it to your Google Drive to make changes please.

https://docs.google.com/spreadsheets/d/1YimEsiDa3gTiKqv7DMohfNvBDruvQ13zrK23Y3R3Gsw/edit?usp=sharing

Upvotes: 0

Views: 1206

Answers (1)

pnuts
pnuts

Reputation: 59485

I suspect the simplest way is the "long-winded" add a column in each sheet (say a new "A") and populate that with the sheet name, so then a query like so:

=query({Data1!A4:C;Data2!A4:C;Data3!A4:C}; "select * where Col2 is not null")

Edit re Comment:

Not the layout required but at least differentiates the source without adding anything to the sources:

 ={query(Data1!A4:B,"select * where A is not NULL label A '"&Data1!B1&"'");query(Data2!A4:B,"select * where A is not NULL label A '"&Data2!B1&"'");query(Data3!A4:B,"where A is not NULL label A '"&Data3!B1&"'")}

Upvotes: 2

Related Questions