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

google sheets

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

Best Answer

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