2

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.

7
  • Why don't you share a doc with us and show us the expected outcome? Jan 5, 2014 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. Jan 5, 2014 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. Jan 6, 2014 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/… Jan 6, 2014 at 20:10
  • @JacobJanTuinstra: The spreadsheet has changed many times during this time, mind you. Jan 6, 2014 at 20:34

1 Answer 1

0

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

3
  • Works just perfectly ! Good work, and thanks. Jan 13, 2014 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. Jan 24, 2014 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. Jan 24, 2014 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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