Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have an inventory spreadsheet with each chest having its own sheet with the sheet numbered and each of those sheets have identical syntax. Rows 1-4 are protected by the separator line, so data starts from row 5:

A|B|C|D|E|F|G|H
<slot number or nothing>|ITEM NAME|TOTAL COUNT OF ITEMS IN ONE STACK|VALUE OF 1 OF THE ITEMS IN COPPERS|TOTAL VALUE OF THE ITEMS IN COPPERS|<...> SILVERS|<...> GOLDS|<...> PLATINUMS

1|clay block|119|0|0|0|0|0
2|tin ore|99|75|7425|74.25|0.7425|0.007425

There's a sheet named -ALL ITEMS-, same syntax as the numbered sheets, which is supposed to make calculation of total counts of each item, for example iron ore. I already tried but couldn't figure out how to check each numbered sheet for the item and add up its count, in C column, to the C column in the -ALL ITEMS- sheet.

My attempt had 2 problems: if a sheet didn't have iron ore, it returned #N/A, and even if there was a row, it returned just that 1 row's TOTAL COUNT OF ITEMS IN THE STACK, not all of them if there was more than 1.

Dunno if you wanna see my code, but I show it just in case. These were in -ALL ITEMS-'s C column:

=INDEX('1'!C5:C9999, MATCH("iron ore", '1'!B5:B9999, 1), 0)=335 =INDEX('1'!C5:C9999, MATCH("iron ore", '1'!B5:B9999, 0), 0)=99 =INDEX('4'!C5:C9999, MATCH("iron ore", '4'!B5:B9999, 0), 0)=99 [because sheets 1 and 4 have iron ore, and 4 has it twice with different TOTAL COUNT IN STACK for each]

=SUM( INDEX('1'!C5:C9999, MATCH("iron ore", '2'!B5:B9999, 0), 0)+INDEX('2'!C5:C9999, MATCH("iron ore", '2'!B5:B9999, 0), 0)+INDEX('3'!C5:C9999, MATCH("iron ore", '3'!B5:B9999, 0), 0)+INDEX('4'!C5:C9999, MATCH("iron ore", '4'!B5:B9999, 0), 0) )=#N/A, error: Did not find value iron ore

EDIT 0: It'd speed up things very much if the item name would be auto-taken from the B column to the left instead of having to be specified in the commands, especially since there are -many- chests and more are coming steadily.

share|improve this question
    
Why don't you share a doc with us and show us the expected outcome? –  Jacob Jan Tuinstra Jan 5 at 19:35
    
@JacobJanTuinstra: What do you mean with 'expected outcome' ? I don't mind if you want to be verbose to make me understand that one. –  rautamiekka Jan 5 at 23:12
    
Well, I'm getting distracted by all the formulas and text you have written down. Sharing a doc (read spreadsheet), with the expected outcome (read: result), often tells more than a thousand words. –  Jacob Jan Tuinstra Jan 6 at 17:05
    
@JacobJanTuinstra: Oh, I see. Sure, I can do that. See the -ALL SHEETS- tab, link after this, it has those formulas written and I wrote iron ore to each so that it's not forgotten what item is in question. docs.google.com/spreadsheet/… –  rautamiekka Jan 6 at 20:10
    
@JacobJanTuinstra: The spreadsheet has changed many times during this time, mind you. –  rautamiekka Jan 6 at 20:34
show 2 more comments

1 Answer 1

up vote 0 down vote accepted

The code below will get all unique names from the sheet names 1 through 14 sheets and perform a summation.

Code

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;
}

Remark

Make a copy of the spreadsheet and goto Tools>Script editor

Example

I've created a copy of your file and added the solution to it: Sum Through Sheets

share|improve this answer
    
Works just perfectly ! Good work, and thanks. –  rautamiekka Jan 13 at 5:38
    
Bug found: if there are multiple items with same name but different sell value, the values are appended together, screwing up the value total calculations. An example: §shackle§, 4 pieces, 0200300300 value of 1. This could be avoided by separating identically named items with different sell value. –  rautamiekka Jan 24 at 4:22
    
Adding to the bug: The script has to know that items without a sell price set means it's not found out yet. Same item with different values just means they're not all of same value. All these differing groups need to be separate groups. –  rautamiekka Jan 24 at 14:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.