0

I have to create variable length sequences from a column along its rows.
Eg- Suppose the column is as below

2
4
3

I want to create a sequence like this

2 | 1 | 2
4 | 1 | 2 | 3 | 4
3 | 1 | 2 | 3

I'm able to create a single sequence using the SEQUENCE function but to spread it over the entire column, I'm not able to use the ARRAYFORMULA correctly.
If I write ARRAYFORMULA(SEQUENCE(A1:A3)), it gives me a column of three ones.

Improve this question
1

1 Answer 1

Reset to default
2

You can create a matrix like this:

=arrayformula( if( sequence(1, max(A1:A)) <= A1:A, sequence(1, max(A1:A)), iferror(1/0) ) )

The formula uses the open-ended reference A1:A, and will thus process all rows in column A. If you want to limit the formula to a predefined number of rows, use a bounded reference like A1:A42.

To limit the formula to the continuous rows where column A contains a number, use filter(), like this:

=arrayformula( if( sequence(1, max(A1:A)) <= filter(A1:A, isnumber(A1:A)), sequence(1, max(A1:A)), iferror(1/0) ) )

To limit the formula to the last row that contains a number, use the max(row() * isnumber()) pattern, like this:

=arrayformula( if( sequence(1, max(A1:A)) <= filter(A1:A, row(A1:A) <= max(row(A1:A) * isnumber(A1:A))), sequence(1, max(A1:A)), iferror(1/0) ) )

A suggested edit proposed to use indirect() for the same purpose. indirect() is a volatile function that gets recalculated every time any value in the spreadsheet is edited. It thus hurts spreadsheet performance and is best avoided.

Improve this answer
12
  • Could you explain the formula? I'm wrapping my head around it for some time but to no avail.
    – Dhruv
    Commented May 23, 2022 at 16:41
  • An arrayformula() wrapper lets you evaluate a formula over a range of cells rather than a single cell, returning multiple results automatically. Also see if(), sequence(), max(), iferror().
    – doubleunary
    Commented May 23, 2022 at 21:52
  • Can you explain the working of arrayformula in this. Like for a single row it will check if each value is less than the max value, if it is, a sequence should start from there right? So accordingly each row should be filled with only ones I guess
    – Dhruv
    Commented May 24, 2022 at 4:07
  • See the ArrayFormula lab blog post by Maxim Makhrov.
    – doubleunary
    Commented May 24, 2022 at 4:30
  • I'm having trouble understanding; why wouldn't sequence(1, max(A1:A)) just work on it's own but wrapping it in an IF function would?
    – David Tan
    Commented May 24, 2022 at 5:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.