3

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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