RedM Posted June 11, 2008 Share Posted June 11, 2008 I’m using Excel 2002 and I have the following problem. I have a list of 8000 part numbers that need to go into a new worksheet. When I paste them into column A they go straight down the page several thousand lines. What I need them to do is go down to the bottom of the first page then carry on at the top of column B and so on until they reach the edge of the page. When they reach the edge they should then start at A:xx where xx is something like line 74 Is this possible? Our IT dept are stumped and I can’t believe no-one has ever wanted to do this before! Can anyone help? Link to comment Share on other sites More sharing options...
MRX Posted June 11, 2008 Share Posted June 11, 2008 Without writing some VB I dont think there is an easy way of doing this... Link to comment Share on other sites More sharing options...
tbourner Posted June 11, 2008 Share Posted June 11, 2008 Where are you copying from? If you can add commas into the list you can do it as a CSV file. Link to comment Share on other sites More sharing options...
Ace Posted June 11, 2008 Share Posted June 11, 2008 I think the only way to do this is to have x-number of part numbers per column (x is a value that you decide), then cut and paste that number of part numbers manually into each column. It's a serious issue with Excel that the program will work out your print previews (and therefore the amount of information per page) for you, but won't manipulate the data for you to fit those pages accordingly. I also get stuck with Excel a lot at work (as my manager is a bit of a doddering old fool and can never remember how to use spreadsheet packages), and I've never found a solution for it. Our IT department are too busy exciting themselves with the latest Blade servers and Mhz figures to worry about actual computing problems Link to comment Share on other sites More sharing options...
tbourner Posted June 11, 2008 Share Posted June 11, 2008 A way to do it a bit quicker than manual paste, is to paste the whole column into A:A, then in B1 put the formula [=A50], doesn't need to be 50, but it's easiest. then in C1 put [=A100], in D1 put [=A150], and so on, until you're on column FD or something. Then you just highlight the formulas in the top row (B1:FD1) and drag fill them down to row 49 to complete them all. Then copy that whole section (A1:FD49) and copy, then 'paste special' - 'values' into another sheet. Would that do? You could use the first sheet for any columns of numbers than as well. Link to comment Share on other sites More sharing options...
Jake Posted June 11, 2008 Share Posted June 11, 2008 A way to do it a bit quicker than manual paste, is to paste the whole column into A:A, then in B:1 put the formula [=A50], doesn't need to be 50, but it's easiest. then in C:1 put [=A100], in D:1 put [=A150], and so on, until you're on column FD or something. Then you just highlight that top row (row 1) and drag it down to row 49 to ... ...screw up the contents of cells A2 to A49 Link to comment Share on other sites More sharing options...
tbourner Posted June 11, 2008 Share Posted June 11, 2008 Well obviously not column A:A, Just the formulas, why would anyone highlight a non-formula cell and drag it to complete!! //edit: Jake's made me look like a knob, so ignore my posts. Link to comment Share on other sites More sharing options...
RedM Posted June 11, 2008 Author Share Posted June 11, 2008 Trev, I'll give your idea a try after lunch. Thank you. Link to comment Share on other sites More sharing options...
Jake Posted June 11, 2008 Share Posted June 11, 2008 Can we use a Macro? Something like this, Mart? Press Ctrl M to run the macro after opening the spreadsheet. I've only done it for Page 1 but hopefully you get the idea. (Done in Excel 2003 but I think it'll run for you) Link to comment Share on other sites More sharing options...
RedM Posted June 11, 2008 Author Share Posted June 11, 2008 We can't use Macros, Jake! All our systems are set to uber secure and can only be changed by IT. Thanks though. Link to comment Share on other sites More sharing options...
Jake Posted June 11, 2008 Share Posted June 11, 2008 Sheesh! Give us all the info mate! :D:D Can we use VB then? Link to comment Share on other sites More sharing options...
JustGav Posted June 11, 2008 Share Posted June 11, 2008 We can't use Macros, Jake! All our systems are set to uber secure and can only be changed by IT. Thanks though. Hack the planet..... Link to comment Share on other sites More sharing options...
RedM Posted June 11, 2008 Author Share Posted June 11, 2008 Sheesh! Give us all the info mate! :D:D Can we use VB then? Victoria Beckham? She an Excel whizz then? Link to comment Share on other sites More sharing options...
Jake Posted June 11, 2008 Share Posted June 11, 2008 Well obviously not column A:A, Ah well, strictly speaking you did say to Highlight Row1 Just the formulas, why would anyone highlight a non-formula cell and drag it to complete!!Actually people drag-fill non-formula cells all the time, it's a perfectly legit thing to do for filling ranges of numbers or dates etc Link to comment Share on other sites More sharing options...
tbourner Posted June 11, 2008 Share Posted June 11, 2008 Good point, damn. OK, I'll edit. //edit: I've even changed my useless cell ID format, cos I had my ':' in the wrong places. Link to comment Share on other sites More sharing options...
Jake Posted June 11, 2008 Share Posted June 11, 2008 We can't use Macros, Jake! All our systems are set to uber secure and can only be changed by IT. Do you work for an Amish company then? Can't you get them to let run YOUR OWN macros? It's not like you're going to exploit0r yourself or steal their meggahurtz. Link to comment Share on other sites More sharing options...
RedM Posted June 11, 2008 Author Share Posted June 11, 2008 Do you work for an Amish company then? Actually I do. I'm compiling an Amish phonebook next. I'll ask them but, considering their response time, it might just be easier to cut n' paste it all manually. Link to comment Share on other sites More sharing options...
Jake Posted June 11, 2008 Share Posted June 11, 2008 Is this a one-off task or something you have to do regularly? Email it me if you like, it'll only take a couple of mins with a macro. SupraTwinTurbo @ g m a i l . c o m Link to comment Share on other sites More sharing options...
RedM Posted June 11, 2008 Author Share Posted June 11, 2008 Is this a one-off task or something you have to do regularly? Email it me if you like, it'll only take a couple of mins with a macro. SupraTwinTurbo @ g m a i l . c o m It's a once a year task. Sometimes twice a year. It's not that I mind doing it the hard way it's more that I can't belive that Excel, after all these years, has no facility to do this easily. Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now