I have a sample data like this:

I want to insert number 1 in 'Identifier' column every 12 weeks BUT counting these weeks from every value equal to 1 in 'Flag' column. So if a the value is 1 in 'Flag' column on 26.1.2021 then we count 12 weeks from there and insert 1 in 'Identifier' column (week 20.4.2021), and so on.. Any formula that can do the job?

CodePudding user response：

You could just write =B2 in cell C14 and drag down.

A bit more sophisticated, starting in D2:

=IF(ROW()>ROW(A$1) 12,INDEX(B:B,ROW()-12),0)

and drag down

or in Excel 365 you could use a spill formula:

=IF(ROW(A2:A28)>ROW(A1) 12,INDEX(B:B,ROW(A2:A28)-12),0)

always assuming your dates in the first column are going up in regular steps of one week.

CodePudding user response：

1. while reading the file first filter the all indexes of '1'(actually row number) s from Flag columns and put into List.
2. then iterate the array along with excel and you can iterate each value from list. and compare each row number with list value 12.
3. Finally filter the row and get the identifier column and fill with 1.

lets say: //sample from above example

       List l ={10,15,23,27};
            for(Integer val :l){
            for(Rows row: file){
              if(val 12 == row.number){
                      //do your logic
             }
          }
        }