How to combine data from different sheets that use the same template?

Question

I have one spreadsheet that has 5 sheets

All sheets have the same template, so they are similar. The only difference is the data.

I would like to make another sheet that gets all the data from column A3 (from row 3 to the end) in sheet 1,2,3,4,5 and puts it in 1 column on my new sheet.

How is this possible?

I use Google Docs, but I think Excel and Google Docs are very similar.

thanks

Answer 1

=CONCATENATE(Sheet1!A:A,",",Sheet2!A:A,",",Sheet3!A:A,",",Sheet4!A:A,",",sheet5!(A:A)) This will be the concatenation of A1, A1, A1, A1, A1 for sheets 1-5 on sheet 6. Drag it down to merge the cells (A2, then A3). You do not need to define A:A ranges, but you can if you want.

Answer 2

So far, the best answer I have found for this question can be found here: http://www.jessespevack.com/systems-leadership/2015/4/22/pulling-spreadsheet-data-no-scripts-required . Essentially, use ImportRange to output data from several other sheets. Then wrap them in ArrayFormula so that they appear one after another on the sheet. Then wrap ArrayFormula in Sort so that the empty lines remain at the end.

Say you have Sheet1, Sheet2, and Sheet3, and you want to merge the AE columns into MergeSheet. Place column headings in MergeSheet cells! A1: E1

Then in cell A2, enter the following formula:

 =SORT( ARRAYFORMULA({ IMPORTRANGE("https://docs.google.com/spreadsheets/d/UniqueKey","Sheet1!A2:E"); IMPORTRANGE("https://docs.google.com/spreadsheets/d/UniqueKey","Sheet2!A2:E"); IMPORTRANGE("https://docs.google.com/spreadsheets/d/UniqueKey","Sheet3!A2:E") ,1,TRUE})) 

The URL is the URL of the spreadsheet spreadsheet and can be copied from the address bar of the browser.

It is best to verify that the IMPORTRANGE function works for each range separately before combining them into one long function.

Answer 3

I do not think you have a built-in function for such functions.

You can easily write Script Applications that runs this

 var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); var newData = new Array(); for (var i = 1; i <= 5; i++) { var s = sheets[i]; var r = s.getRange("A:A"); // Go over the values and fill up a new range in sheet 6... var data = r.getValues(); for(i in data){ var row = data[i]; newData.push(row); } } 

Answer 4

This is the easiest solution to combine multiple sheets of multiple Google spreadsheets (with the same columns)

 =SORT( { IMPORTRANGE("unique_spreadsheet_key1";"'Your first sheet'!A2:G"); IMPORTRANGE("unique_spreadsheet_key2";"'Your second sheet'!A2:G"); IMPORTRANGE("unique_spreadsheet_key2";"'Your third sheet'!A2:G") }; 3; TRUE ) 

Based on dcb solution , but fixed (part of SORT) and without ARRAYFORMULA!

• You can change 3 to sort your merged sheet by the column you need!
• unique_spreadsheet_key: key only, does not execute the full url