# Repeat X times when dragging down

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.

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