Back
Excel

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?