+ Reply to Thread
Results 1 to 5 of 5

Convert data from rows to columns

  1. #1
    boksic
    Guest

    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:
    [email protected]
    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: / [email protected])
    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. #2
    Anne Troy
    Guest

    Re: Convert data from rows to columns

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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:
    > [email protected]
    > 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: / [email protected])
    > 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. #3
    Ed Ferrero
    Guest

    Re: Convert data from rows to columns

    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:
    > [email protected]
    > 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: / [email protected])
    > 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. #4
    boksic
    Guest

    Re: Convert data from rows to columns

    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:
    > > [email protected]
    > > 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: / [email protected])
    > > 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. #5
    boksic
    Guest

    Re: Convert data from rows to columns

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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:
    > > [email protected]
    > > 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: / [email protected])
    > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1