Repeating a Set of Data

re: Repeating a Set of Data

AJ — Wed, 02 Jun 2010 16:08:39 GMT

Wow, i cant tell you how helpful this is for my job.

I do data entry, and this will save many wasted hours of overcomplicated formulas/templates.

re: Repeating a Set of Data

to Joseph — Fri, 06 Feb 2009 08:05:37 GMT

Excellent.....very helpful

Gurvinder, Accounts Manager

re: Repeating a Set of Data

SteveA — Sat, 31 Jan 2009 18:26:37 GMT

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.

re: Repeating a Set of Data

Joseph Chirilov — Fri, 30 Jan 2009 22:04:27 GMT

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

re: Repeating a Set of Data

SteveA — Fri, 30 Jan 2009 18:00:42 GMT

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 $

re: Repeating a Set of Data

Harlan Grove — Mon, 26 Jan 2009 23:49:45 GMT

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

=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.

re: Repeating a Set of Data

Per Erik — Mon, 26 Jan 2009 22:57:55 GMT

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.

re: Repeating a Set of Data

Nigel Harper — Mon, 26 Jan 2009 18:19:12 GMT

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!

re: Repeating a Set of Data

Russian Match — Fri, 23 Jan 2009 07:10:38 GMT

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

re: Repeating a Set of Data

Joseph Chirilov — Fri, 23 Jan 2009 01:38:40 GMT

.c.: Nice touch!