Search code examples
google-sheetslambdacountsequenceflatten

Repeat one SEQUENCE value if another CELL is not empty - Google Sheet

I've been trying to figure this out, but I had no success until now.

I found how to repeat a specific sequence (forever), how to show a sequence (1 to N) value only if another cell is not empty. But I can't find a solution to repeat a specific sequence (1 to 11 as many time as necessary) value only if a cell is NOT empty.

The table will be updated by a Google Form, the empty cell to check would be the timestamp.

Here is a image showing the idea

Here is a link to a working sheet, with the idea hard-coded

Solution

  • try:

    =ARRAY_CONSTRAIN(FLATTEN(MAKEARRAY(ROUNDUP(COUNTA(A2:A)/11); 11; 
 LAMBDA(x; y; y))); COUNTA(A2:A); 1)

    enter image description here

    or:

    =INDEX(ARRAY_CONSTRAIN(FLATTEN(
 SEQUENCE(ROUNDUP(ROWS(A:A)/11); 11)-(
 SEQUENCE(ROUNDUP(ROWS(A:A)/11); 1; 0)*11)); COUNTA(A2:A); 1))

    enter image description here