How to combine content from several tables into one?-CodePudding

this is the first time I'm posting here.

I'm trying to make a spreadsheet for my work on Google Sheets and I want to make a table with all the information from 5 other tables without having to copy and paste it.

I have 5 tables, all of them have the same number of columns and the same type of data in each column. I need to make a new table that shows all the data from all the tables in one.

To make it simpler let's suppose I have 3 tables A, B and C Each of the tables are like this:

Table A:

DATE|DESCRIPTION|PRICE
1/10|AAAAAAAAAAA|10000
2/10|BBBBBBBBBBB|20000
3/10|CCCCCCCCCCC|30000

Table B:

DATE|DESCRIPTION|PRICE
4/10|DDDDDDDDDDD|40000
5/10|EEEEEEEEEEE|50000
6/10|FFFFFFFFFFF|60000

Table C:

DATE|DESCRIPTION|PRICE
7/10|GGGGGGGGGGG|70000
8/10|HHHHHHHHHHH|80000
9/10|IIIIIIIIIII|90000

I would like to make a table D with the information from tables A, B and C like this:

Table D:

DATE|DESCRIPTION|PRICE
1/10|AAAAAAAAAAA|10000
2/10|BBBBBBBBBBB|20000
3/10|CCCCCCCCCCC|30000
4/10|DDDDDDDDDDD|40000
5/10|EEEEEEEEEEE|50000
6/10|FFFFFFFFFFF|60000
7/10|GGGGGGGGGGG|70000
8/10|HHHHHHHHHHH|80000
9/10|IIIIIIIIIII|90000

Also if I add one more row in any of the tables I want table D to update as well automatically either by adding the new row at the end or in another position... It doesnt really matter as long as it appears in table D.

How can I do this with a formula without having to copy and paste it?

Thank you so much!

CodePudding user response：

Use query() with an { array expression }, like this:

=query( 
  { 
    Sheet1!A1:C; 
    Sheet2!A2:C; 
    Sheet3!A2:C 
  }, 
  "where Col1 is not null", 
  1 
)

CodePudding user response：

You can use query for that

=QUERY({Sheet1!A2:C; Sheet2!A2:C; Sheet3!A2:C},"Select * where Col1 is not Null",0)

Please note while combining array, make sure to use similar range reference, that means row and column size of each array reference should be same otherwise it will throw Reference Error

Reference

Query