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

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"
<[email protected]> 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

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

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

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

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"
> <[email protected]> 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
>