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 rowsum(A$n:An)
. Perhaps I can make use of this along with theoffset
androw
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).
Best Answer
A50
toA250
,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.