 |
 | 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 / General Excel Questions / March 2008Transpose not working rows to columns
Hi, I have a spreadsheet with many rows of data and 2 columns of data. Format is like this with the row names differing at times between contacts: Last Name Smith First Name A. Middle Name B. Job Title Boss Company ABC Company Business Street 123 Main St Business City Anywhere Business State CA Business Postal Code 90888 Business Phone (555) 123-4567 Business Fax (555) 123-4576 E-mail Address absmith@ABC.com Notes no notes right now Last Name Jones First Name R. Middle Name U. Job Title Sr. Boss Company XYZ Company Business Street 987 East St Business City Wheresthat Business State CA Business Postal Code 98009 Business Phone (555) 123-0987 Business Fax (555) 765-1234 E-mail Address RUJones@xyz.com I've tried using transpose but when I select the rows of data, Excel put all of the data in the first two rows (what appears on the left of above example are all headings in the first row, what appears on the right of the above example all appear on the same row but they are different contacts that should be on separate rows), it does give column headings but I want row A1 across to be the column headings from the rows, and rows B2 on down to be the data. Thanks for any help. Let's say that your 2 column datalist is on Sheet1, from A1 to Bn, and you want to create your newly configured list on Sheet2. First, on Sheet1, select A1 to A13. Right click in the selection and choose "Copy". Navigate to Sheet2, and right click in A1, and choose "Paste Special". Click on "Transpose", then <OK>. This will give you your column headers. Now in A2 of Sheet2, enter this formula: =INDEX(Sheet1!$B:$B,14*ROWS($1:1)+COLUMNS($A:A)-14) Copy this formula across 14 columns to N2 (Column N will represent the blank row between contacts). Then, copy the 14 column selection (A2 to N2) down as far as needed. This should give you the type of display you're looking for.  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Hi, I have a spreadsheet with many rows of data and 2 columns of data. > Format is like this with the row names differing at times between [quoted text clipped - 39 lines] > data. > Thanks for any help. Thank you, that worked very well. You do have to make sure all of the rows are consistent for each contact i.e. First Name, Middle Name, Last Name, Suffix, Title, etc. When they are consistent, this formula works perfectly. > Let's say that your 2 column datalist is on Sheet1, from A1 to Bn, and you > want to create your newly configured list on Sheet2. [quoted text clipped - 60 lines] > > data. > > Thanks for any help. Yes ... a consistent data source contributes immensely to everything performing as planned. Appreciate the feed-back. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Thank you, that worked very well. You do have to make sure all of the > rows [quoted text clipped - 72 lines] >> > data. >> > Thanks for any help. |
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.