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?

• Joseph,

I play around with baseball and football stats in excel quite a bit and what you've done is exactly the way I'd do it.  Writing a macro for something like that would slow me down.  When I do something like this, I don't necessarily do your step 3 calculation, however.

• I've had a similar problem but tackled it quite differently. I used this formula to calculate which row item was required:

=INT((ROW()-2)/5)+1

The five refers to the number of times I wanted each row repeated. I then used a second column with this formula to show the repeated data:

=OFFSET(\$A\$1,\$G2,0)

where my raw data was stored in Column A and the offset value calculated previously was stored in Column G.

A bit more complicated but I did not want to sort the data into a different order than the original.

Derek

• Better? I'm not sure but here's how I handled it. Column A has the values to repeat and the repeating column is in column B.

I seeded B1 with the first value, 1000, then I used the following formula in B2 and dragged down:

=IF(COUNTIF(\$B\$1:B1,B1)

• I'd go with Derek's approach as being most flexible. (Ivan's approach can't handle duplicates).

• there are two similar solutions i can suggest

(a) For small quantity of number in column A

=INDEX(A:A,INT(1+ROW(A1)/(4.0001)))

(b)For large quantity of numbers (if you have like 4000 numbers in column A )

=INDEX(A:A,INT(1+ROW(A1)/(4+1/COUNTA(A:A))))

these both are actually same. initially thought if i could generte cell ref dynamically like A & Int(1+1/4) = A1 and A & Int(1+2/4) = A1 and so on.

• Only difference I'd do is once i typed in the list of numbers, hold the control key down and drag the numbers down to the required repeated.

Then ending with a sort as above.

Laura

• I have written a simple UDF to perform this task, and also extract rows at a specified spacing (e.g every third row), and the same for columns.  It will also extract specified rows or columns, where the row or column numbers are specified in a spreadsheet range.  See: newtonexcelbach.wordpress.com/.../repeating-a-set-of-data

• Step 2, 3, 4 can be replaced with:

Press and hold CTRL, and use fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle with pressed CTRL, the pointer changes to a black cross and plus sign)... Also try CTRL+ALT.

• Creative solution. I am a psychology student and I will be sure to use this for my reports and experiments.  Thank you thank you thank you

• What do you think about the following solution:

Put the values 1000, 5000, 600 in cells A1:A3 and the following formula in B1:

=INDEX(\$A\$1:\$A\$3,ROUNDUP(ROW()/4,0))

and then copy the formula down.

• Jurgen: That doesn't work for me when I try it.  It #REFs after the 12th row.

I found the following formula also works but doesn't produce a "bucketized" list, and the only way to sort is to first copy/past values:

=INDEX(\$A\$1:\$A\$3,MOD(ROW()-1,COUNTA(\$A\$1:\$A\$3))+1)

Thanks everyone for your creative solutions!

• I need to keep the numbers in their original order, so I insert a column to the right, put a 1 in the first row, drag the sequence to the last number in the list:

1000  ... 1

5000  ... 2

600   ... 3

I copy both columns, proceed as before, but sort on the sequence column in order to retain the original order. Delete the inserted column and you are home and dry.

• .c.: Nice touch!

• This blog has been created to share useful information. Thanks and greetings!

• For repeating rows:

=INDIRECT("R"&ROW(\$A\$1)+INT((ROWS(\$B\$1:\$B1)-1)/4)&"C"&COLUMN(\$A\$1),0)

For repeating columns:

=INDIRECT("R"&ROW(\$A\$1)&"C"&COLUMN(\$A\$1)+INT((COLUMNS(\$A\$2:A\$2)-1)/4),0)

\$A\$1 is the top-left cell of the series you want to repeat.

\$B\$1 (for rows) or \$A\$2 (for columns) - either can be any cell - is the cell you enter this formula into (no, even though this is self-referencing it does not result in a circular calculation).

4 is the number of times you want to repeat each successive value.

No sorting required, but blanks do show up as zeros.

For repeating both rows & columns (probably best entered on a different sheet):

=INDIRECT("Sheet1!R"&ROW(Sheet1!\$A\$1)+INT((ROWS(\$A\$1:\$A1)-1)/4)&"C"&COLUMN(Sheet1!\$A\$1)+INT((COLUMNS(\$A\$1:A\$1)-1)/4),0)

Sheet1!\$A\$1 is the top-left cell of the series you want to repeat.

\$A\$1 (for rows and columns) - can be any cell - is the cell you enter this formula into (again, no, even though this is self-referencing it does not result in a circular calculation).

4 is the number of times you want to repeat each successive value (can be a different value for rows and columns.

Again, no sorting required, but blanks do show up as zeros.

IMPORTANT:  Be sure to get the 'dollarizing' correct!