# How to write a formula returning from specific

Good day to everybody.

I am working on a Google Sheet. Here I have a somewhat non standard table. To cut it short, I was able to designate "1" in column A for each row that fulfills my requirement.

Imagine that the "1" is spread in column A in the following cells:

A7 / A9 / A11 / A12 / A14 / A15 / A16 / A19 / A21 /

and so on...

what I would like to have a formula is that in column B it will return:

b7=1 / B9=2 / b11 AND B12=3 / B14, 15, 16=4 / B19=5

the idea is that on each row with a single text value assigned, it will return 1, blank columns will return blank and succeeding consecutive rows with values will be counted as the same next number, if i am making any sense

hope you guys can help me on this.

good day and regards

Mike

• Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Jul 19 at 17:35

Assuming that you have put the number 1 in several rows of column A2:A, put this formula in row 2 of a free column:

=let(
data, A2:A,
ordinals, scan(0, data, lambda(a, c,
if(c = 1, a + (offset(c, -1, 0) <> 1), a)
)),
map(data, ordinals, lambda(d, o,
if(d, o, iferror(ø))
))
)


See let(), scan(), offset() and map().