Suppose we have a list of positive integers along column A:


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

1 Answer 1

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


  • 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 :)
    – WaterGenie
    Mar 3, 2018 at 9:15
  • What does it mean when a matrix is "lesser" than a range of value?
    – WaterGenie
    Mar 3, 2018 at 9:32
  • 1
    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.
    – WaterGenie
    Mar 3, 2018 at 9:46
  • Let us continue this discussion in chat.
    – WaterGenie
    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?
    – WaterGenie
    Mar 3, 2018 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.