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.
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:
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?
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:
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:
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.
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:
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
(b)For large quantity of numbers (if you have like 4000 numbers in column 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.
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:
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:
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:
For repeating columns:
$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):
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!