Looping through a set of google sheet values

问题:

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
  1
  1
  1
  1
  1
  0
  0
  1
  1
  1
  1
  1
  1
  1
  0
  0
  1
  1
  1
  0
  1
  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.But if 0 comes in between then it should start again from A1 .. like this

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

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

Hope to get some help to get this solved. Thanks

回答1:

You can do it most easily with a helper column

=if(B4=1,if(B3=0,0,mod(E3+1,4)),"")

starting in E4 then

=if(E4<>"",index(A$4:A$7,E4+1),"")

starting in F4.

Here's one way of doing it with a single non-array formula:

=IF(B4=1,INDEX(A$4:A$7,MOD(SUM(B4:INDEX(B:B,MAX(INDEX(IF(B$4:B4=0,ROW(B$4:B4)),0)),0))-1,4)+1),"")

in (say) G4 and pulled down.

NB both of these assume that the sequence in column B starts with a zero and would need adjustment if this is not the case.

An array formula is also possible:

=ArrayFormula(if(B4:B=1,vlookup(mod(sumif(row(B4:B),"<="&row(B4:B),B4:B)-sumif(row(B4:B),"<="&vlookup(row(B4:B),if(B4:B=0,row(B4:B)),1),B4:B)-1,4)+4,{row(4:7),A4:A7},2,false),""))

EDIT

Probably the easiest way to get it to work when the first row of on/off data contains a 1 is to include the header row in column B and use n() to treat it as zero so the helper column formula is

=if(B4=1,if(n(B3)=0,0,mod(E3+1,4)),"")

and the non-array formula is

=IF(B4=1,INDEX(A$4:A$7,MOD(SUM(B4:INDEX(B:B,MAX(INDEX(IF(n(B$3:B4)=0,ROW(B$3:B4)),0)),0))-1,4)+1),"")

The array formula works without any change. This is for the fortuitous reason that, while the second Vlookup will fail with #N/A if there are no zeroes in column B before the current row, when this is passed to Sumif you get a zero result which is what you need.

回答2:

Edit

Single formula solution:

=FILTER(IF(B1:B=0,"",VLOOKUP(IFERROR(VLOOKUP(SUMIF(ROW(B1:B),"<="&ROW(B1:B),B1:B),{ROW(INDIRECT("a1:a"&COUNTIF(B:B,1))) , TRANSPOSE(SPLIT(TEXTJOIN("",1,TRANSPOSE(TEXT(ROW(INDIRECT("a1:a"&MAX(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0")))))*(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0"))>=ROW(INDIRECT("a1:a"&MAX(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0")))))),"0"","";;"))),",")) },2,),0),{row(INDIRECT("a1:a"&COUNTIF(B1:B,1))) , VLOOKUP(MOD(ROW( INDIRECT("a1:a"&COUNTIF(B1:B,1)) )-1,COUNTA(A1:A))+1,{ROW(A1:A)-row(A1)+1,A1:A},2,)},2,)),B1:B<>"")

---

Two formulas will do it.

#1 in C1. For cumulative sum with a loop:

=Filter(IFERROR(VLOOKUP(SUMIF(ROW(B1:B),"<="&ROW(B1:B),B1:B),{ROW(INDIRECT("a1:a"&COUNTIF(B:B,1))) , TRANSPOSE(SPLIT(TEXTJOIN("",1,TRANSPOSE(TEXT(ROW(INDIRECT("a1:a"&MAX(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0")))))*(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0"))>=ROW(INDIRECT("a1:a"&MAX(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0")))))), "0"","";;"))),",")) },2,),0),B1:B<>"")

#2 in D1. For the result:

=FILTER(IF(B1:B=0,"",VLOOKUP(C1:C,{row(INDIRECT("a1:a"&COUNTIF(B1:B,1))) , VLOOKUP(MOD(ROW( INDIRECT("a1:a"&COUNTIF(B1:B,1)) )-1,COUNTA(A1:A))+1,{ROW(A1:A)-row(A1)+1,A1:A},2,)},2,)),B1:B<>"")

References:

1. Counters Lab
2. Array Formulas Lab