# 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.

• Incidentally, my max(A:A) is just 60, I'll edit the question to mention this. Still reading the rest of your answer, hang on :) – Irin Thirdwater Mar 3 '18 at 9:15
• What does it mean when a matrix is "lesser" than a range of value? – Irin Thirdwater Mar 3 '18 at 9:32
• Ohhh, ok wait, please double check if I understood this correctly: You first create a matrix with count(A:A) rows and max(A:A) columns and each of the rows are just populated with the `1,2,3,...,max(A:A)`. Then for each row in the matrix, we leave the number (and add a lollipop for the concat&split business) if the column is still less than the corresponding integer in A:A. I think I got the basic idea now, but still quite lost on how array formula put this all together with the matrix thing, but that's another exercise. – Irin Thirdwater Mar 3 '18 at 9:46
• – Irin Thirdwater Mar 3 '18 at 9:55
• I don't quite understand the `index(A:A,B2):index(Z:Z,B7)`, where is the `B2`, `Z:Z`, and `B7` coming from? – Irin Thirdwater Mar 3 '18 at 13:20