I need to split one row of data into multiple rows. I have one column which consists of a list of companies and Next to it I have a series of attributes for every year. I need to transpose the years into a single column and next to each year I need the values of the attributes. Please have a look at the attached file..thanks ! Would appreciate immediate help, really urgent ..
Try this Array Formula. Since this is an array formula, so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)
In C21
and then drag right up to R21 and down to R199.
Please Login or Register to view this content.
For detail see the attached sheet.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Hi Thank you very much for the reply. Can you just tell me how do I follow the steps... I selected cells from c21 to r21and to r199 and then I pasted the formula on the formula bar and then pressed Ctrl Shift+ Enter..It shows 2610 on all the cells after I did this..thanks once again
Follow these steps.........
1) Copy the formula from the post#2
2) Select the cell C21 in your sheet and delete if it has anything in it and then press F2 (function key)
3) Ctrl+V to paste the formula in cell R21
4) hold down Ctrl+Shift and then press Enter. The active cell is still C21. You will see the in the formula bar that the formula is surrounded by the curly braces.
5) Now grab the fill handle of cell C21 (bottom right corner of the cell boundary) and drag the formula up to R21.
6) You will see now that the formula is applied to the range C21:R21.
7) While the whole range C21:R21 is still selected.
8) Grab the fill handle of cell R21 (bottom right corner of the cell boundary of R21 and drag it down up to R199.
9) This way you will see that the formula is now applied to the range C21:R199.
And that's it.
I hope that you know how to drag the formula of a cell to its right or down.
Does this help.
Last edited by sktneer; 04-22-2014 at 09:46 PM.
Thank you so much for the explanation! I just have a couple of more questions: if I have more data(more number of companies and rows) how would I apply the same? Also, any easy way to paste the company name and the years repeatedly with the required spacing? Thank you once again, really appreciate your prompt response!
Glad to help you. Thanks for the feedback.
You have to do this manually and to apply the formula, you need to drag down the formula up to the last row of col. R (instead of R199) and the steps are the same, it depends on how many rows will be there.
If that takes care of your question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
do i need to change the formula in case i have different number of rows?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks