Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Convert data from rows to columns
I have a range of information on a spreadsheet as follows:
A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / ) I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns. Any suggestions would be very helpful. I have tried using a macro on one entry but am stuck as to how to apply it to repeat over a range of data |
#2
|
|||
|
|||
I hate to say it, but the fastest way, without formulas... copy to Word. It
should paste as a table. Then, click on the table, and choose Table--Convert--Table to text. Tell it to use Paragraph returns. I hope you'll then have a records with one line between each. If so, hit Ctrl+H and put: ^p^p in the Find What box. Put PARARETURN in the Replace With Box. Replace all. Now, Find ^p and replace with ^t. Then Find PARARETURN and replace with ^p. Now select all the lines again (and nothing extra!) and hit Table--Convert--Text to table. Click anywhere in the table. Hit Table--Select--Table. Copy and paste back into Excel. Delete unwanted columns. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "boksic" wrote in message ... I have a range of information on a spreadsheet as follows: A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / ) I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns. Any suggestions would be very helpful. I have tried using a macro on one entry but am stuck as to how to apply it to repeat over a range of data |
#3
|
|||
|
|||
Hi boksic,
This is a common enough problem when importing from report files. One relatively simple way of doing it - Name the first cell in the data list "StartCell" Go to a new Sheet In cell A2 enter 0 In cell A3 enter 8 Select both cells, then click on the handle at the bottom right of the selection box and drag down a few rows. This will fill a series (0,8,16,24...) In cell B1 enter 0 In cell C1 enter 1 In cell D1 enter 2 In cell B2 enter =OFFSET(StartCell,$A2+B$1,0) Copy cell B2 as needed Ed Ferrero http://edferrero.m6.net I have a range of information on a spreadsheet as follows: A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / ) I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns. Any suggestions would be very helpful. I have tried using a macro on one entry but am stuck as to how to apply it to repeat over a range of data |
#4
|
|||
|
|||
Great - thanks a lot for this Ed
"Ed Ferrero" wrote: Hi boksic, This is a common enough problem when importing from report files. One relatively simple way of doing it - Name the first cell in the data list "StartCell" Go to a new Sheet In cell A2 enter 0 In cell A3 enter 8 Select both cells, then click on the handle at the bottom right of the selection box and drag down a few rows. This will fill a series (0,8,16,24...) In cell B1 enter 0 In cell C1 enter 1 In cell D1 enter 2 In cell B2 enter =OFFSET(StartCell,$A2+B$1,0) Copy cell B2 as needed Ed Ferrero http://edferrero.m6.net I have a range of information on a spreadsheet as follows: A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / ) I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns. Any suggestions would be very helpful. I have tried using a macro on one entry but am stuck as to how to apply it to repeat over a range of data |
#5
|
|||
|
|||
Thank you very much for your help Anne. I have tried something that somebody
else suggested but I will certainly bear your suggestion in mind for future problems similar to this. "Anne Troy" wrote: I hate to say it, but the fastest way, without formulas... copy to Word. It should paste as a table. Then, click on the table, and choose Table--Convert--Table to text. Tell it to use Paragraph returns. I hope you'll then have a records with one line between each. If so, hit Ctrl+H and put: ^p^p in the Find What box. Put PARARETURN in the Replace With Box. Replace all. Now, Find ^p and replace with ^t. Then Find PARARETURN and replace with ^p. Now select all the lines again (and nothing extra!) and hit Table--Convert--Text to table. Click anywhere in the table. Hit Table--Select--Table. Copy and paste back into Excel. Delete unwanted columns. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "boksic" wrote in message ... I have a range of information on a spreadsheet as follows: A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / ) I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns. Any suggestions would be very helpful. I have tried using a macro on one entry but am stuck as to how to apply it to repeat over a range of data |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort column data with hidden columns - excel 2003 | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
How to keep rows together when sorting columns? | Excel Worksheet Functions | |||
change data in rows to be viewed in columns | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into excel? | Excel Discussion (Misc queries) |