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

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

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

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

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