Google Sheets – How to Repeat X Times When Dragging Down

google sheets

I have a list of (unique) numbers which I want to drag on another column but have it repeat each an X number of times.

Column A: current data;

Column B: desired output for X=2;

--------------------------------------------------
|   |    A     |     B                            |
--------------------------------------------------
| 1 |  Number  |  Repeat number twice             |
--------------------------------------------------
| 2 |   123    |     123                          |
--------------------------------------------------
| 3 |   231    |     123                          |
--------------------------------------------------
| 4 |   444    |     231                          |
--------------------------------------------------
| 5 |   312    |     231                          |
--------------------------------------------------
| 6 |   543    |     444                          |
--------------------------------------------------

I want a way of dragging down starting at B2 all the way down to B:1000 and repeat each number in column A an X amount of times.

Best Answer

It's possible with a rather simple formula. Enter this formula in the first cell you want to drag from, and then just drag down.
=INDIRECT("A"&(ROUNDUP(ROW(A1)/2)+ROW(A$2)-1))

Explanation

  • INDIRECT() takes a string argument and returns a cell reference
  • "A"& just tells us which column to look for values in
  • ROUNDUP(ROW(A1)/2) is what gives is the repeating row numbers
    • It always starts on row 1, which gives us 1/2 rounded up = 1
    • Next time 2/2 rounded up = also 1
    • Then 3/2 rounded up = 2
    • 4/2 = 2
    • And so forth
    • The reason for using a cell reference is for the number to increase when dragging down.
  • +ROW(A$2)-1 moves down to the specific row.
    • In this case we move down 1 row (2-1)
    • In most cases this could be set to the cell above the first value (+ROW(A$1)), but it wouldn't work when the value is in the first row

Modification

You'd have to modify this if the cells aren't exactly as in your example.

  • The string A refers to the column with the values that should be repeated
  • A2 refers to the cell in the first row in the column (row 1, in any column really, not the first row with a value)
  • A$2 is the first cell with a value

If, for example, your first value is in B12 you change it to:
=INDIRECT("B"&(ROUNDUP(ROW(B1)/2)+ROW(B$12)-1))

Related Topic