formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryworksheet-function
I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets that looks like:
Sheet 1
Sheet 2
Sheet 3
What's the best way to accomplish this? If easier, I do not mind having the header show up 3 times. Thank you.
The code below will get all unique names from the sheet names 1 through 14 sheets and perform a summation.
function getTotals() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var uniqueNames = [], allData = [], output = [];
// itterate through the sheets and retrieve info
for(var i=1; i<14; i++) {
this.sh = ss.getSheetByName(i);
this.lRow = this.sh.getMaxRows();
this.names = this.sh.getDataRange().getValues();
var a = this.names;
// gather unique names and data
for(var j=4, jLen=a.length; j<jLen; j++) {
if(a[j][1].length > 0) {
if(uniqueNames.indexOf(a[j][1]) == -1) {
uniqueNames.push(a[j][1]);
}
allData.push([a[j][1],a[j][2],a[j][3]]);
}
}
}
// sort all the data for efficient processing
var sData = allData.sort(), sNames = uniqueNames.sort();
// itterate throught the names and perform sum
for(var l=0, lLen=sNames.length; l<lLen; l++) {
var sum1=0, sum2=0;
for(var m=0, mLen=sData.length; m<mLen; m++) {
if(sNames[l] == sData[m][0]) {
sum1 += sData[m][1], sum2 += sData[m][2];
}
}
output.push([sNames[l], sum1, sum2]);
}
return output;
}
Make a copy of the spreadsheet and goto Tools>Script editor
I've created a copy of your file and added the solution to it: Sum Through Sheets
={{QUERY({Sheet1!E3:F; Sheet2!E3:F; Sheet3!E3:F; Sheet4!E3:F; Sheet5!E3:F},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1
label Col1 'Month', sum(Col2)'Sum'", 0)},
{"Sheet1"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet1!E3:F, 2, 0), ))},
{"Sheet2"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet2!E3:F, 2, 0), ))},
{"Sheet3"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet3!E3:F, 2, 0), ))},
{"Sheet4"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet4!E3:F, 2, 0), ))},
{"Sheet5"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet5!E3:F, 2, 0), ))}}
=QUERY({Sheet1!E3:F; Sheet2!E3:F; Sheet3!E3:F; Sheet4!E3:F; Sheet5!E3:F},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1
label Col1 'Month', sum(Col2)'Sum'", 0)
={{"Sheet1"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet1!E3:F, 2, 0), ))},
{"Sheet2"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet2!E3:F, 2, 0), ))},
{"Sheet3"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet3!E3:F, 2, 0), ))},
{"Sheet4"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet4!E3:F, 2, 0), ))},
{"Sheet5"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet5!E3:F, 2, 0), ))}}
Best Answer
Something like this should work