Wednesday, October 7, 2015

Google Sheet Select items by column that contains few parameters separated by comma

Question:

Hi everyone, I am trying to create some sort of inventory database using google sheets. I have one sheet 'Inventory' that lists all the items in my database. In another sheet 'Search' I want to have an ability to display items that qualify size dispersion. The problem is that items have multiple sizes listed in one cell separated by comma.

TimestampNameWeightPriceTire sizes
07.10.2015 11:04:01Chain 1220100200, 300
07.10.2015 14:04:44Chain 2135350100
07.10.2015 14:05:27Chain 3453560100, 200, 300

I need to come up with formula that would take following input

Size fromSize to
50120

And would display items that qualify this input dispersion like

TimestampNameWeightPriceTire sizes
07.10.2015 14:04:44Chain 2135350100
07.10.2015 14:05:27Chain 3453560100, 200, 300


Solution:

Following is the screenshot of Sheet "Inventory"


Now, put the following script in the script editor of your spreadsheet:


function inventory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Inventory');
  var r = s.getRange('A:E');
  var v = r.getValues();
  var lastrow  = getLastPopulatedRow(v);
  var arr = new Array();
  arr.push(v[0]);
  
  for(var i=1;i<lastrow;i++) {
    var e = v[i][4].toString().split(',');
    for(var j=0;j<e.length;j++){
      arr.push( [ v[i][0],v[i][1],v[i][2],v[i][3],e[j].trim()*1 ] );
    }
  }
  return arr;
};

function getLastPopulatedRow(data) {
  for (var i=data.length-1;i>=0;i--)
    for (var j=0;j<data[0].length;j++)
      if (data[i][j]) return i+1;
  return 0;
};


save the above code and then refresh your spreadsheet.

Now, just try the following custom function in a new sheet in your spreadsheet:
=inventory()

The above custom function will give you the following output:


Now, we can use this output in a Query formula, try the following formula in cell A2 of sheet "Search":
=query(inventory();"select * where Col5>="&A2&" and Col5<="&B2;1)

Following is the screenshot of Sheet "Search":



2 comments:

  1. Thanks for your answer. I've tried this solution and it seems to work. Although when you choose sizes between let's say 120 and 350 it would output same items twice like this
    07.10.2015 Chain 1 220 100 200
    07.10.2015 Chain 1 220 100 300
    07.10.2015 Chain 3 453 560 200
    07.10.2015 Chain 3 453 560 300

    ReplyDelete
    Replies
    1. The output that you got, has different "Tire sizes", if you don't want "Tire sizes" in the result then you can try the following formula:
      =unique(query(inventory();"select Col2,Col3,Col4 where Col5>="&A2&" and Col5<="&B2;1))

      which will give you the output as:

      Name Weight Price
      Chain 1 220 100
      Chain 3 453 560

      Delete