Search code examples
google-sheetsgoogle-sheets-formula

How could I move/offset the sequence in google sheets?

so let's say I have these things in the first column:

apple banana cherry socks

If I put "TRUE" in column B next to any of them, the numbering should start from that position. when it reaches the end (so colmn A has no value), it must go to the start and continue the numbering there.

This sounds complicated and stupid, so for example

A B C
apple 1 TRUE
banana 2
cherry 3
socks 4

if I put "TUE" in the 3rd row, then:

A B C
apple 3
banana 4
cherry 1 TRUE
socks 2

Is this even possible to achieve? If so, how? I've been doing research on this problem and found nothing, but dozens of explanations of "SEQUENCE" (which does not resolve the issue).

Solution

  • Here's another solution:

    =LET(a,MATCH(TRUE,B:B),b,COUNTA(A:A)-a+1,TOCOL({SEQUENCE(a-1,1,b+1);SEQUENCE(b)},3))

    enter image description here