LinkBack URL
About LinkBacks
Hello,
So I need to figure out how to get excel to take a column of numbers and create a new column of number that repeats every number 3 times. Attached is an example of what I mean. Thank you in advance for you help.
Thanks,
Kelly Peterson
Hello,
So I need to figure out how to get excel to take a column of numbers and create a new column of number that repeats every number 3 times. Attached is an example of what I mean. Thank you in advance for you help.
Thanks,
Kelly Peterson
Try this simple macro
Please Login or Register to view this content.
Martin
Put this formula in B1:
=INDEX(A:A,INT((ROWS(A$1:A1)-1)/3)+1)
then copy down to B12 (or further if you have more data in column A).
Hope this helps.
Pete
Put this formula in B1:
=INDEX(A:A,INT((ROWS(A$1:A1)-1)/3)+1)
then copy down to B12 (or further if you have more data in column A).
Hope this helps.
Pete
This works great but how do I adapt it because in my actual workbook my data starts on row 5 instead of row 1.Originally Posted by Pete_UK
for pete's sake, instead of A:A, use A5:A100 (or however far data stretch in column A)...
do not worry about changing the 'ROWS(A$1:A1)' portion of the formula.
Put this in B5, then:
=INDEX(A:A,INT((ROWS(A$1:A1)-1)/3)+5)
and copy down.
Hope this helps.
Pete
I tried that it breaks it. Then all it does is copy line for line it doesn't repeat.for pete's sake
do not worry about changing the 'ROWS(A$1:A1)' portion of the formula.
That worked perfectly thank you so much for your help!
sorry, @kellyp, i should have been more specific. i tweaked pete_uk's formula thus:
=INDEX($A$5:$A$100,INT((ROWS(A$1:A1)-1)/3)+1)
and it worked for me when i tested it - because it had the '$'.
glad that @pete_uk showed a better way in his own formula.
good learning for me...
Last edited by icestationzbra; 04-03-2012 at 01:18 PM.
Okay, Kelly, glad to hear that. Please mark the thread as Solved (the FAQ shows how) and click the "star" icon in the bottom left corner of any post that has helped you,
Pete
my data is Heading wise and i want repeat headings up to down form, this formula =INDEX(A:A,INT((ROWS(A$1:A1)-1)/3)+1) is giving me result well but my one requirement is not full fill so plz advise me if any solution.
Mmudasir,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks