CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
S. Vins
S. Vins

Reputation: 3

Copy row into new sheet(s) if two different cell values are within a defined range

I would like to create a Google Spreadsheets document that maintains:

I created a sample doc for reference that's editable here.

Fairly limited programming/script experience.

Upvotes: 0

Views: 80

Answers (2)

David Tew
David Tew

Reputation: 1471

I have added a formula to both sheets to achieve something like you may want

i.e. the first one below is the OR and the second is the AND

=filter('Sheet 1 - All Entries'!A:C,(('Sheet 1 - All Entries'!B:B>=5000) + ( 'Sheet 1 - All Entries'!C:C >=5000)))  
=filter('Sheet 1 - All Entries'!A:C,(('Sheet 1 - All Entries'!B:B>=5000) * ( 'Sheet 1 - All Entries'!C:C >=5000)))

PS... I am not sure if a formula is sufficient for you or if you wanted this done through script

Upvotes: 0

pointNclick
pointNclick

Reputation: 1632

In case you were looking for a script. This is what it'll look like although you'll have to do the mapping yourself. I haven't opened your doc but this will make it work if you need a script to do your bidding.

function sheetMaintain()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("Sheet1");
  var s2 = ss.getSheetByName("Sheet2");
  var s3 = ss.getSheetByName("Sheet3");

  var r1 = s1.getDataRange();
  var r2 = s2.getDataRange();
  var r3 = s3.getDataRange();

  var data1 = r1.getValues();


  for (var i=0; i<r1.getLastRow(); i++)
  {
    if (data1[i][1]>=5000 || data[i][2]>=50000)
    {
      var colB = data[i][1];
      var colC = data[i][2];

      var lr2 = r2.getLastRow();

      s2.getRange(lr2+1, 1).setValue(colB); //Mapping Col B value to Col A of Sheet 2
      s2.getRange(lr2+1, 2).setValue(colC); //Mapping Col C value to Col B of Sheet 2
    }

    if (data1[i][1]>=5000 && data[i][2]>=50000)
    {
      var colB = data[i][1];
      var colC = data[i][2];

      var lr3 = r3.getLastRow();

      s3.getRange(lr3+1, 1).setValue(colB); //Mapping Col B value to Col A of Sheet 3
      s3.getRange(lr3+1, 2).setValue(colC); //Mapping Col C value to Col B of Sheet 3
    }
  }
}

if you'd like to not have any duplicates (from running the function time and again) in Sheet2 or Sheet3 I would also suggest you look into using the clear() function and set your Sheet2 and Sheet3 to clear before you parse through Sheet1 again.

Upvotes: 1

Related Questions