CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-vlookup
Shijilal
Shijilal

Reputation: 2169

Looping through a set of values in google sheets

I have 2 sets of data. One is tank names

Tank Name  
  A1
  A2
  B1
  B2

and the next is ON/OFF Data

ON/OFF
  0
  0
  1
  1
  0
  1
  0
  1
  1
  0
  1
  0
  1

Now the result I am looking is, when the ON/OFF is 1 then the first tank is to be mentioned: when it's 0, no tank to be mentioned. Once all the tanks are mentioned,then it should again start from the first tank ie A1..like this

Result expected
 0  
 0  
 1  A1
 1  A2
 0  
 1  B1
 0  
 1  B2
 1  A1
 0  
 1  A2
 0  
 1  B1

You can check the google sheet here : https://docs.google.com/spreadsheets/d/1SP2SfA-bzzhHgfrvpyUIkeQfUykata0oHxyD-x69yxE/edit?usp=sharing

Hope to get some help to get this solved. Thanks

Upvotes: 1

Views: 53

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34265

You can use this formula entered in (say) D2 and pulled down:

=if(B2=1,index(A$2:A,mod(sum(B$2:B2)-1,4)+1),"")

or if you prefer, can go for an array formula entered in E2 instead:

=ArrayFormula(if(B2:B=1,vlookup(mod(sumif(row(B2:B),"<="&row(B2:B),B2:B)-1,4)+2,{row(2:5),A2:A5},2,false),""))

enter image description here

Upvotes: 5

Related Questions