1

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

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Shijilal
  • 2,175
  • 10
  • 32
  • 55

1 Answers1

5

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

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Thanks for helping me out. If you could help me in this similar one too,it will be very helpful. https://stackoverflow.com/questions/52963740/looping-through-a-set-of-google-sheet-values – Shijilal Oct 24 '18 at 08:01
  • No problem. I'll have a look at the other one later - certainly do-able, but probably not with an array formula. – Tom Sharpe Oct 24 '18 at 09:02