Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Melissa
 
Posts: n/a
Default How do I create multiple columns from a one-column list in Excel?

I have a one-column list with similar types of row data, that I would like to
use to create a mulit-column spreadsheet. For example, the list currently
looks like this:

Example A
text
text
text

Example B
text
text
text

Every row is currently in column A. What I would like to do, without having
to copy and paste (I have several hundred rows of text) is to have the data
look like:

Example A Text Text Text
Example B Text Text Text

I have looked at functions such as concatenate and Pivot tables but they
don't seem to do what I need to do.

I would appreciate any help with this.

Melissa
Queensland, Australia
  #2   Report Post  
Myrna Larson
 
Posts: n/a
Default

I am going to assume that each block consists of the "header" plus 3 rows of
text, then a blank row (5 rows per block) and the data starts in A1.

Put this formula in B1, and copy it to the right, through E1. Then copy B1:E1
down until the formulas begin to return 0's (i.e. you've run out of data in
column A).

=INDEX($A:$A,(ROW()-1)*5+COLUMN()-1)

Then Edit/Copy all of the formulas, and, without changing the selection,
Edit/Paste Special and select the Values option. Once you've done that, you
can delete the original data in column A.

If there's no blank row, change the 5 in the formula to 4, and just copy
across to D1.

If the number of rows in a block is not constant, you will probably need a VBA
macro.



On Tue, 4 Oct 2005 18:00:01 -0700, "Melissa"
wrote:

I have a one-column list with similar types of row data, that I would like to
use to create a mulit-column spreadsheet. For example, the list currently
looks like this:

Example A
text
text
text

Example B
text
text
text

Every row is currently in column A. What I would like to do, without having
to copy and paste (I have several hundred rows of text) is to have the data
look like:

Example A Text Text Text
Example B Text Text Text

I have looked at functions such as concatenate and Pivot tables but they
don't seem to do what I need to do.

I would appreciate any help with this.

Melissa
Queensland, Australia

  #3   Report Post  
PY & Associates
 
Posts: n/a
Default

Assuming "Example A" is in cell A1
put "a1" in B1, "a2" in C1, "a3" in D1, "a4" in E1
likewise put "a6" in B2, "a7" in C2, "a8 in D2 "a9"in E2
highlight B1 to E2 and drag copy down to get each row in the number
increases by 5
that is down column B, you get a1, a6, a11 etc

with this new block highlighted, substitute "a" with "=a"

if you are happy with the result, delete column A

"Melissa" wrote:

I have a one-column list with similar types of row data, that I would like to
use to create a mulit-column spreadsheet. For example, the list currently
looks like this:

Example A
text
text
text

Example B
text
text
text

Every row is currently in column A. What I would like to do, without having
to copy and paste (I have several hundred rows of text) is to have the data
look like:

Example A Text Text Text
Example B Text Text Text

I have looked at functions such as concatenate and Pivot tables but they
don't seem to do what I need to do.

I would appreciate any help with this.

Melissa
Queensland, Australia

  #4   Report Post  
PY & Associates
 
Posts: n/a
Default

Make sure you copy/paste special/value before deleting column A please

"PY & Associates" wrote:

Assuming "Example A" is in cell A1
put "a1" in B1, "a2" in C1, "a3" in D1, "a4" in E1
likewise put "a6" in B2, "a7" in C2, "a8 in D2 "a9"in E2
highlight B1 to E2 and drag copy down to get each row in the number
increases by 5
that is down column B, you get a1, a6, a11 etc

with this new block highlighted, substitute "a" with "=a"

if you are happy with the result, delete column A

"Melissa" wrote:

I have a one-column list with similar types of row data, that I would like to
use to create a mulit-column spreadsheet. For example, the list currently
looks like this:

Example A
text
text
text

Example B
text
text
text

Every row is currently in column A. What I would like to do, without having
to copy and paste (I have several hundred rows of text) is to have the data
look like:

Example A Text Text Text
Example B Text Text Text

I have looked at functions such as concatenate and Pivot tables but they
don't seem to do what I need to do.

I would appreciate any help with this.

Melissa
Queensland, Australia

  #5   Report Post  
Melissa
 
Posts: n/a
Default

Hi PY & Associates

Your reply has now given me a second way of sorting the data. I certainly
appreciate your help.

Cheers
Melissa

"PY & Associates" wrote:

Make sure you copy/paste special/value before deleting column A please

"PY & Associates" wrote:

Assuming "Example A" is in cell A1
put "a1" in B1, "a2" in C1, "a3" in D1, "a4" in E1
likewise put "a6" in B2, "a7" in C2, "a8 in D2 "a9"in E2
highlight B1 to E2 and drag copy down to get each row in the number
increases by 5
that is down column B, you get a1, a6, a11 etc

with this new block highlighted, substitute "a" with "=a"

if you are happy with the result, delete column A

"Melissa" wrote:

I have a one-column list with similar types of row data, that I would like to
use to create a mulit-column spreadsheet. For example, the list currently
looks like this:

Example A
text
text
text

Example B
text
text
text

Every row is currently in column A. What I would like to do, without having
to copy and paste (I have several hundred rows of text) is to have the data
look like:

Example A Text Text Text
Example B Text Text Text

I have looked at functions such as concatenate and Pivot tables but they
don't seem to do what I need to do.

I would appreciate any help with this.

Melissa
Queensland, Australia



  #6   Report Post  
Melissa
 
Posts: n/a
Default

Hi Myrna

Thanks so much for your very prompt reply. Each block does have differing
numbers of rows so I will need to do a VBA Macro.

Thanks again

Cheers
Melissa

"Myrna Larson" wrote:

I am going to assume that each block consists of the "header" plus 3 rows of
text, then a blank row (5 rows per block) and the data starts in A1.

Put this formula in B1, and copy it to the right, through E1. Then copy B1:E1
down until the formulas begin to return 0's (i.e. you've run out of data in
column A).

=INDEX($A:$A,(ROW()-1)*5+COLUMN()-1)

Then Edit/Copy all of the formulas, and, without changing the selection,
Edit/Paste Special and select the Values option. Once you've done that, you
can delete the original data in column A.

If there's no blank row, change the 5 in the formula to 4, and just copy
across to D1.

If the number of rows in a block is not constant, you will probably need a VBA
macro.



On Tue, 4 Oct 2005 18:00:01 -0700, "Melissa"
wrote:

I have a one-column list with similar types of row data, that I would like to
use to create a mulit-column spreadsheet. For example, the list currently
looks like this:

Example A
text
text
text

Example B
text
text
text

Every row is currently in column A. What I would like to do, without having
to copy and paste (I have several hundred rows of text) is to have the data
look like:

Example A Text Text Text
Example B Text Text Text

I have looked at functions such as concatenate and Pivot tables but they
don't seem to do what I need to do.

I would appreciate any help with this.

Melissa
Queensland, Australia


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to merge data from multiple columns to one column w8ting4hlp Excel Discussion (Misc queries) 5 June 23rd 05 12:07 AM
can i create multiple columns from the info in one cell HawaiianTux Excel Discussion (Misc queries) 2 April 11th 05 05:50 PM
Is it possible to create one column with two different width? Dave H... Excel Worksheet Functions 1 February 21st 05 06:52 PM
Is it possible to create one column with two different width? Dave H... Excel Worksheet Functions 0 February 21st 05 06:45 PM
Pivot Table combining multiple columns Pete Petersen Excel Discussion (Misc queries) 1 January 13th 05 08:56 PM


All times are GMT +1. The time now is 11:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"