 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2006Lots of rows and columns into just rows
I have a sheet of data 3000+ rows with 8 columns in each. I need to adjust the data into 24000 rows of one column. I want to take the first row of the old sheet (8 columns) and make the contents the first 8 rows of the new sheet (1 column), the old sheet second row to be new rows etc...9-16 Something to do with Paste Special but not sure. Any help please? Cheers, Bodster Say data is on Sheet1, from A1 to H3000. Enter this formula *anywhere* you wish on another sheet, and copy down 3000 rows: =INDEX(Sheet1!$A$1:$H$3000,ROWS($1:8)/8,MOD(ROWS($1:1)-1,8)+1)  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > I have a sheet of data 3000+ rows with 8 columns in each. I need to adjust > the data into 24000 rows of one column. I want to take the first row of the [quoted text clipped - 8 lines] > > Bodster Typo Patrol! Maybe "copy down 24000 rows"? Gord >Say data is on Sheet1, from A1 to H3000. > >Enter this formula *anywhere* you wish on another sheet, and copy down 3000 >rows: > >=INDEX(Sheet1!$A$1:$H$3000,ROWS($1:8)/8,MOD(ROWS($1:1)-1,8)+1) Gord Dibben MS Excel MVP OK Gord - I'll bite! WHY copy down 24,000 rows? SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Typo Patrol! > [quoted text clipped - 10 lines] > > Gord Dibben MS Excel MVP Never mind - It just hit me! You're absolutely right.<bg> SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Typo Patrol! > [quoted text clipped - 10 lines] > > Gord Dibben MS Excel MVP Thanks RD I was going to pull out the TI calculator to test my 24000 number<g> Gord >Never mind - It just hit me! > >You're absolutely right.<bg> If we want to really be picky, we should say - "Copy down 23,999 rows" - right? SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Thanks RD > [quoted text clipped - 5 lines] > > > >You're absolutely right.<bg> Thank you so much for that, it'll save loadsa time. I adjusted the formula as the original sheet contined 11 columns of data. Can you help with this part now? The original sheet had dates in Column E (in the form Jan-03). Thay have come through as 5 digit figures such as 37622. Is there any way of getting the original form of the date back, affecting just rows 5, 16, 27 etc...of the new sheet? A similar problem exists with column I of the original where the £ sign has been lost. Again is there any way of getting this sign back in the new sheet just on rows 9, 20, 31 etc.. Cheers, Bodster > Say data is on Sheet1, from A1 to H3000. > [quoted text clipped - 18 lines] >> >> Bodster The formats will not come across with the formula returns. What you'll have to do however, is not too difficult. Since you say you have 11 columns, format the *first set* of 11 rows on your new sheet exactly as you wish them to display. Select these 11 rows. Click on the 'format painter' (yellow paint brush) icon in your tool bar. Click in the row 12 cell (*not* the fill handle) and drag down as needed. You'll see that the formats of the original 11 rows will duplicate down the column in the proper sequence. SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Thank you so much for that, it'll save loadsa time. I adjusted the formula > as the original sheet contined 11 columns of data. [quoted text clipped - 35 lines] > >> > >> Bodster |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.