Repeating a Set of Data

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!