1

I've created several small sheets, all with the same number of columns, and I'd like to produce one large sheet consisting of all the rows from the smaller sheets. I don't want to perform any deduplication, since each row contains important data.

Is there any way I can do this? The closest thing I've been able to find is:

={'Sheet1'!A:D, 'Sheet2'!A:D}

The trouble here is that the columns are appended to one another, not the rows. Meanwhile, if I do this (note the semicolon instead of the comma):

={'Sheet1'!A:D; 'Sheet2'!A:D}

I get the data from the first sheet and no data from the second.

4

Give row numbers instead of column letters, and separate the ranges by semicolon ; (vertical stacking instead of horizontal).

={Sheet1!1:3; Sheet2!2:7}

This assumes both sheets 1 and 2 have the same number of columns. If this is not the case, you'll need to specify which columns you want:

={Sheet1!A1:Z3; Sheet2!A2:Z7}

To get all rows of another sheet, use a construction with indirect:

=arrayformula({Sheet1!1:4; indirect("Sheet2!1:" & max(row(Sheet2!A:A)))})

Here, "Sheet2!1:" & max(row(Sheet2!A:A)) constructs a string such as "Sheet2!1:1000", with the latter number obtained as the maximum of row numbers.

  • How do I select all rows? Also, I tried to use a semicolon instead of a comma and it only included data from the first sheet listed. Note I've updated my question to reflect this. – Alex Jan 3 '17 at 22:09
  • I've included "all rows" options in the answer. Using a semicolon while still selecting columns results in columns stacked one on top of the other. So you got all the empty cells at the bottom of the first sheet, and never scrolled down enough to see the other. – user135384 Jan 3 '17 at 22:18
  • I must be missing something. See here for a demo where I'm pretty sure I'm doing what you're recommending for row-wise concatenation but it isn't working: docs.google.com/spreadsheets/d/… – Alex Jan 4 '17 at 0:59
  • 1
    Okay never mind, I just understood the meaning of "never scrolled down far enough." Sure enough, the rows are there further down. – Alex Jan 4 '17 at 1:09
2

Formula

=FILTER({Sheet1!A:D; Sheet2!A:D},LEN({Sheet1!A:A; Sheet2!A:A}))

Explanation

Doing operations with open ended references usually introduces a lot of blank rows or columns. If you are not aware of this, this could lead to confusions, as it look to happened to the OP.

Besides the use of the array notation, consider to filter the resulting array to avoid having several blank rows/columns between the appended data. There are several ways to do this, one of them is by using FILTER as was shown on the first section.

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.