Displaying an ordered list column based on specific cell numeric value

[dheerajravi]

I apologize for the title, but I really can't find a good way to way to describe what I want to do. I will do my best.

Suppose cell A1 has a numeric value of 4. I want column B to have values 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, and so on, where each number is in a different cell (i.e. cells B1-B4 have a value of 1, B5-B8 have a value of 2, etc.

If I change the numeric value in A1 to 2, then I want column B to have values 1, 1, 2, 2, 3, 3, 4, 4, and so on. So how many times each integer repeats in column B is based on the value in A1.

I wish I could tell you what I have done, but I have no clue where to start. I am relatively new to Excel (only started learning a month ago). Any suggestions would be appreciated.

Thanks.

 

[Gary's Student]

In B1 enter:

=ROUNDUP(ROW()/$A$1,0) and copy down

 

[Peter_SSs]

Welcome to the MrExcel board!

I would caution against using ROW() in the way Gary's Student has suggested. If you subsequently insert a new row 1 (or multiple rows), say to add headings, then the formula will return incorrect results. That could, however, be overcome with this slight modification:
=ROUNDUP(ROWS(B$1:B1)/$A$1,0)

Another option would be to place the number 1 in cell B1, then use this formula in B2 and copy down:
=B1+(MOD(ROWS(B$1:B1),A$1)=0)

 

[Gary's Student]

I agree. Using ROWS() is a definite improvement!

 

[dheerajravi]

Welcome to the MrExcel board!

I would caution against using ROW() in the way Gary's Student has suggested. If you subsequently insert a new row 1 (or multiple rows), say to add headings, then the formula will return incorrect results. That could, however, be overcome with this slight modification:
=ROUNDUP(ROWS(B$1:B1)/$A$1,0)

Another option would be to place the number 1 in cell B1, then use this formula in B2 and copy down:
=B1+(MOD(ROWS(B$1:B1),A$1)=0)

Thank you Peter and Gary for your help with this. It is exactly what I wanted.