CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-api
Nathan Gartner
Nathan Gartner

Reputation: 21

Google Sheet Filter odd and even cells to another sheet

I'm building a weather station to track Growing Degree Days for when to apply plant growth regulator. it's all ready but am having on problem. I have a tab labeled "History" and need the odd number cells like A1,A3,A5,A7 to move that data to another tab called "High Low Temp Option". If that makes sense. for example I need A1 from "History" to move to K11 on "High Low Temp Option" tab. And just keep repeating like A3 to K12 and A5 to K13 and so on. Then I need to do the same thing to the evens so B2 to L11, B4 to L12 and so on. I'm not sure how to set up this equation for this.

Thanks!

Upvotes: 0

Views: 492

Answers (3)

z..
z..

Reputation: 12943

I need A1 from "History" to move to K11 on "High Low Temp Option" tab. And just keep repeating like A3 to K12 and A5 to K13 and so on. Then I need to do the same thing to the evens so B2 to L11, B4 to L12 and so on.

I'll assume History is the tab name. You can do this cleanly with a filter too.

In K11:

=filter(History!A:A,mod(row(History!A:A),2)=1)

or

=filter(History!A:A,isodd(row(History!A:A)))

In L11:

=filter(History!B:B,mod(row(History!B:B),2)=0)

or

=filter(History!B:B,iseven(row(History!B:B)))

Upvotes: 0

Yuri Khristich
Yuri Khristich

Reputation: 14537

Just in case, here is the script that copies the values from every second row from 'History' sheet to your first sheet into columns 'K' and 'L':

function copy_from_history_to_first_sheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sh_src = ss.getSheetByName('History');
  var data = sh_src.getDataRange().getValues().filter((_,i) => i%2 == 0);
  var col_A = data.map(x => [x[0]]);
  var col_B = data.map(x => [x[1]]);

  var sh_dest = ss.getSheetByName('High Low Temp Option');
  sh_dest.getRange('K11:K' + (col_A.length+10)).setValues(col_A);
  sh_dest.getRange('L11:L' + (col_B.length+10)).setValues(col_B);
}

Upvotes: 1

p._phidot_
p._phidot_

Reputation: 1952

in K11 put :

=indirect("Sheet1!A"&(row()-11)*2+1)

and drag downwards.

similarly, in L11 put :

=indirect("Sheet1!B"&(row()-11)*2+2)

and drag downwards.

That should do. (Sheet1 <-- chg to ur tab name)

Pls share if it works/not/understandable.

Upvotes: 3

Related Questions