| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 72603 | 2006-09-19 03:31:00 | Excel - how to quickly redo the structure? | Nomad (952) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 485708 | 2006-09-19 03:31:00 | Hi, just one question now. For excel. If one has like 10yrs of monthly data which you wanna make it quarterly. If all data is in A column. You wanna leave rows 1 2 3 unchanged, then 4 5 6 into B column then 7 8 9 into C etc etc... What's the best way to do it? I have tried paste special transpose but that is not able to do it in each (3), would a macro do it? Is a macro able to start at any row? Thanks. |
Nomad (952) | ||
| 485709 | 2006-09-19 08:39:00 | For only 10 years (120 cells) I could have dragged the 40 x 3 cell clusters and dropped them into place in the time it took to write the question. A macro would take longer again to write, compared to the simple drag and drop, you could keep moving the column A values up to save a lot of scrolling. |
godfather (25) | ||
| 485710 | 2006-09-19 08:48:00 | Hi with data in a list you can use pivot tables etc to get quarterly if there were columns for year and quarter. Currently Excel can have 65,536 rows but only 256 columns. So once you get to 256 x 3 rows your going to start having problems. Yes, a macro can do pretty much anything you can do manually but obviously a tad quicker. Not sure it may be the best option but thats up to you :-) If you still want a macro you will need to advise what happens when theres no more columns available etc. regards, Graham |
Parry (5696) | ||
| 485711 | 2006-09-19 09:19:00 | Hmm.. thanks. I don't have any work right now. I did some work before and thought there might be a better way than all those labour work. Last time I had like 120 rows of monthly data and 4 rows of different currencies to pop into quarter. It was also performed under a test scenario. I thought about working across so right under the 3 rows I could pop one formulae and the next one across (cell A and B) and then auto fill it right across. |
Nomad (952) | ||
| 485712 | 2006-09-19 21:26:00 | Hi Nomad, I have to do Income Support figures for myself, wifey and joint. To do your 120 sums, just keep to "copy and paste" diagonally down, but yours is going to be very cumbersome once you stretch it down. I used to write formulae for a manufaturing company a long time ago, that was Lotus Symphony!. =SUM($H4:$H6) =SUM($H7:$H9) =SUM($H10:$H12) Good luck, Lurking |
Lurking (218) | ||
| 1 | |||||