0

I have several tables on the same sheet with different numbers of rows and columns but basically have the same structure, now I want data from these different tables to be united in one master database.

I have tried using an array query but the result that the table gets is shifted sideways instead of downwards, how to handle this?

1

Can you provide an example sheet to clarify the tables you want to merge and how you want the data portrayed?

You said you used an “array query” but I’m not clear on what you mean. A query produces an array. Have you tried an “array of queries”? This would look like this:

={(query(sheet1!A1:C3, “select *”));(query(sheet2!A1:C3, “select *”))}

I’m on mobile but you get the point. * would be replaced with your query. Depending on whether you us a ; or , to separate the queries within the array will determine whether the two queries are put together from top to bottom or from left to right.

EDIT:

Here is an example of merging 3 different sheets. You will have to adjust the queries and, of course, the sort is optional. The outer query is to remove blank rows from getting into the final output.

= SORT( QUERY( { (QUERY(Sheet1!A1:Z, "select *") ; (QUERY(Sheet2!A1:Z, "select *") ; (QUERY(Sheet3!A1:Z, "select *") }, "select * where Col1 is not null"), 1, TRUE)

6
  • Hello, thank you for your answer. ={(query(sheet1!A1:C3, “select *”));(query(sheet2!A1:C3, “select *”))} was precisely what I do. But the return was from left to right; I want it to be top to bottom.
    – hanna
    Nov 5 at 2:02
  • Change the semicolon between the two queries to a comma. Instead of “{query;query}” try “{query,query}”
    – Kevin
    Nov 5 at 2:31
  • I updated the original answer.
    – Kevin
    Nov 5 at 2:50
  • Hello, thank you for your answer. It's helpful.
    – hanna
    Nov 5 at 5:35
  • “select *” is the default select clause and isn't required. So your last (SORT) formula could be written as = SORT( QUERY({Sheet1!A1:Z;Sheet2!A1:Z;Sheet3!A1:Z}, "select * where Col1 is not null"), 1, TRUE). On the other hand, if you plan to use the select clause then a comma between the range and the query is required.
    – Tedinoz
    Nov 5 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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