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
There are about 200-300 integers in column A and each of them could go up to 60.
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+1starts will start on row
sum(A$n:An). Perhaps I can make use of this along with the
rowfunction 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).