CODEWITHSUNDEEP
excelgoogle-docs
Kenci
Kenci

Reputation: 4882

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

I have one spreadsheet which has 5 sheets

All the sheets share the same template, so they are alike. The only difference is the data

I would like to make another sheet, whichs 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 in my new sheet.

How is this possible?

I am using Google Docs, but i guess Excel and Google Docs are much alike.

Thanks

Upvotes: 5

Views: 29094

Answers (4)

didier2l
didier2l

Reputation: 305

This is the simpliest solution to merge 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 corrected (the SORT part) and without ARRAYFORMULA!

  • You can change 3 to sort your merged sheet by the column of your need !
  • unique_spreadsheet_key : just the key, doesn't neet complete url

Upvotes: 0

DCB
DCB

Reputation: 541

By far the best answer I've found to this question is found here: http://www.jessespevack.com/systems-leadership/2015/4/22/pulling-spreadsheet-data-no-scripts-required. In essence use ImportRange to pull in data from several other sheets. Then wrap those in an ArrayFormula so that they appear one after the other on the sheet. Lastly wrap the ArrayFormula in a Sort so that the blank lines are all left at the end.

Say you have Sheet1, Sheet2 and Sheet3 and you would like to combine columns A through E on MergeSheet. Put the column headings in the cells MergeSheet!A1:E1

Then in cell A2 enter a formula like this:

=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, and can be copied from the address bar of the browser.

It's best to test that the IMPORTRANGE function works for each range individually before combining them into the one long function.

Upvotes: 3

Guy
Guy

Reputation: 12929

I don't think that you have a built-in function for such functionality.

You can easily write an Apps Script that performs 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);
  }
}

Upvotes: 0

Raystafarian
Raystafarian

Reputation: 3032

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

Upvotes: 5

Related Questions