# Repeating a Set of Data

I thought this would be interesting to share to see how other Excel folks might have solved this problem.  I got a question recently from a customer asking how to automatically take a set of values in one column, say:

1000
5000
600

and repeat each value, say, 4 times in the next column, so that it looks something like this:

1000
1000
1000
1000
5000
5000
5000
5000
... and so on.

My response: the only way to truly do this automatically is to write a custom macro.  However, if you can suffer through a few extra clicks, then my suggestion would be to (using the example data above):

1. Select the three input cells (e.g. A1:A3)
2. Select “Copy” (Ctrl+C)
3. Select the next 9 cells underneath these inputs (A4:A12)
4. Select “Paste” (Ctrl+V)
5. Click the Sort button to sort the data into 3 distinct buckets (600, 1000, 5000).

For step #3, the number of cells you select will always be: NumberOfInputs x NumberOfRepetitions – NumberofInputs.  In this case: 3 x 4 – 3 = 9.

That seemed to work well enough for the customer.  What do you think?  Is there another (better?) way?

• This is the solution I came up with:

=OFFSET(A\$1;ROUNDUP(ROW()/4;0)

-1;0)

Change /4 to whatever number of times you want it to repeate.

• A previous reply I sent didn't show up. I'll repeat it.

If the source data were in a range named D, and you wanted each entry in D repeated K times, and the first result cell were X99, try

X99:  =INDEX(D,ROUNDUP(ROWS(X\$99:X99)/K,0))

Fill X99 down as far as needed. The formula will return #REF! errors after D has been exhausted.

It's also possible to return the repeated instances as an array using either

=N(OFFSET(D,(ROW(INDIRECT("1:"&(K*ROWS(D))))-1)/K,0,1,1))

or

=T(OFFSET(D,(ROW(INDIRECT("1:"&(K*ROWS(D))))-1)/K,0,1,1))

depending on whether D contains numbers or text.

And I asked the question before: what does an expression like

OFFSET(A1,{0;0;1;1;2;2},0,1,1)

return? It acts like an array of range references.

• How about this. Type the numbers you want in a row rather than column eg, 1000 in B2, 5000 in C2, 600 in D2 etc.

Then select the cells and copy-drag the right hand corner down for the number of repetitions. So, for example, you now have 1000 in B2, B3, B4, B5, 5000 in C2, C3, C3 etc

Then select each block of numbers and move under the first ones, so 5000 in C2:C4 to B6:B9 etc

It's fairly quick for small numbers and the lack of a sort operation does kill any quirky ordering.

BTW I have a question. As an example, if I have a formula in cell B9 that adds the contents of cells B5 and B6 eg "=B5+B6".

Is there any way I can copy (not move) that formula to another cell, say D9, but so that the formula stays pointing to the original cells, eg still "=B5+B6"

I don't want change the formula to fixed addressing by adding \$

• SteveA: Usually what I do in that case is press F2 to put the cell in edit mode, select the entire formula and copy it to the clipboard (using CTRL+C), exit edit mode (ESC), then select the target cell (e.g. D9) and paste (CTRL+V).

• To Joseph

Thanks, I appreciate your suggestion. That's seems pretty obvious, doh!

There is always something new to learn, and sometimes it is can be something almost elementary.

So thanks again Joseph.