Merging data in google sheets from multiple sheets with varying number of rows

1
votes

I have multiple google sheets with each containing variable number of rows but fixed set of columns. The number of rows can change in any sheet with time. How can i create summary sheet with data from all sheets placed one after the other? eg. If sheet 1 has 5 rows and sheet 2 has 6, i want the summary sheet to have 11 rows with 1-5 containing data from sheet 1 and 6-11 data from sheet 2.

2
google-sheets
fyi, you need to show some effort in figuring out the answer before asking. – user27636
Yeah, I was new to the forum & didn't know this then. Point noted. – Mohit

2 Answers

3
votes
= {
    FILTER(NamedRange1, NamedRange1Col1 <> '');
    Filter(NamedRange2, NamedRange1Col1 <> '')
  }

Please create a named range for each sheet. This ranges will comntain your data. Within each named range, also create a named range column. This can be the first column of every named range, or any column.

We are using the filter function FILTER so that you do not import empty rows. You dont have to use it though. The curly braces are semicolon are the crucial parts. In general, you "stack" multiple arrays as follows:

= {Array1; Array2;...;Array n}
0
votes

the correct syntax is: 

={FILTER(Sheet1!A:C; Sheet1!B:B<>"");
  FILTER(Sheet2!A:C; Sheet2!B:B<>"")}

and another solution would be:

=QUERY({Sheet1!A:C; Sheet2!A:C}; "where Col2 is not null", 0)