# Google-sheets – Expanding a column of integers into a column of ranges from 1 to each of the integers

## Prompt

Suppose we have a list of positive integers along column A:

`````` A
---
2
1
3
2
``````

My goal is to generate a list of ranges from 1 to each of the integers in that column:

`````` A                   B
---                 ---
2 ──────────────┬─> 1
1 ───────────┐  └─> 2
3 ───────┐   └────> 1
2 ───┐   └──────┬─> 1
└──────┐   ├─> 2
|   └─> 3
└───┬─> 1
└─> 2
``````

Edit
There are about 200-300 integers in column A and each of them could go up to 60.

## Attempt

I am juggling between these functions with no success so far:

• Row – Using `=ArrayFormula(row(indirect("A1:"&A1)))`, I can get each of the desired ranges individually. However, I couldn't seem to nest more array formulas in order to combine them into one column.
• Sum and Offset – The range corresponding to cell `An+1` starts will start on row `sum(A\$n:An)`. Perhaps I can make use of this along with the `offset` and `row` function to calculate my "position" in the resulting column. But I also couldn't quite piece them together the way we need.
• Rept – Each integer in column A determines the number of times we "repeat" in the range corresponding to that integer. But this led me to a dead end where I couldn't change each individual items being repeated (so that it counts up the required number of times instead of just repeating one value the required number of times).

``````=ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(IFERROR(IF((IF(row(A1:A50)=TRANSPOSE(ROW(A1:A50)),ROW(A1:A50),TRANSPOSE(ROW(A1:A50))))<=A1:A4,(IF(row(A1:A50)=TRANSPOSE(ROW(A1:A50)),ROW(A1:A50),TRANSPOSE(ROW(A1:A50))))& "🍭",))),"🍭")))
``````
• Will work upto max(a:a)<=50 and count(a:a)<=50; Will work more than that with modifications below
• Change all `A50` to `A250`,If max(A:A) or count(a:a)=250.

How it works

• Creates a virtual `1,2,3...` matrix with this formula:(Here,5 is the max(A:A))

`ARRAYFORMULA(IF(row(A1:A5)=TRANSPOSE(ROW(A1:A5)),ROW(A1:A5),TRANSPOSE(ROW(A1:A5))))`

• If the matrix is lesser than given `A1:A4` values, Take only those values with a lollipop.

• concatenate the whole matrix ,split the lollipop and transpose to give your array.