# 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 :) Mar 3, 2018 at 9:15
• What does it mean when a matrix is "lesser" than a range of value? Mar 3, 2018 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. Mar 3, 2018 at 9:46
• Mar 3, 2018 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? Mar 3, 2018 at 13:20