You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
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:
10005000600
and repeat each value, say, 4 times in the next column, so that it looks something like this:
10001000100010005000500050005000... 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):
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?
Comments: (22) Collapse
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.
Excellent.....very helpful
Gurvinder, Accounts Manager
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.
Comments: (loading) Collapse