Data from Rows into columns

[BCLivell]

I have an excel worksheet that someone provide me. All of the information is
in 1 column (A) with each row having the person's contact info. The pattern
repeats for each contact. The pattern like this

A1-Company name
A2-Name
A3-phone

There are 100 contacts in this format. I need to seperate all of the
information so that the comany name is one column and the name is an another
column and the phone is in a third column. I can not figure out how to do
this. Thank you!!!

[Alan Beban]

If the functions in the freely downloadable file at
http//home.pacbell.net/beban are available to your workbook

=ArrayReshape(A1:A300,100,3) array entered into B1:D100

Alan Beban

BCLivell wrote:
> I have an excel worksheet that someone provide me. All of the information is
> in 1 column (A) with each row having the person's contact info. The pattern
> repeats for each contact. The pattern like this
>
> A1-Company name
> A2-Name
> A3-phone
>
> There are 100 contacts in this format. I need to seperate all of the
> information so that the comany name is one column and the name is an another
> column and the phone is in a third column. I can not figure out how to do
> this. Thank you!!!
>

[Ronco]

BC,
Try highlighting column A. Then from the top menus, select Data, Text to
Columns, delimited, Next and, then, depending on whatever is separating the
info, select the delimiter. Then Finish.


"BCLivell" wrote:

> I have an excel worksheet that someone provide me. All of the information is
> in 1 column (A) with each row having the person's contact info. The pattern
> repeats for each contact. The pattern like this
>
> A1-Company name
> A2-Name
> A3-phone
>
> There are 100 contacts in this format. I need to seperate all of the
> information so that the comany name is one column and the name is an another
> column and the phone is in a third column. I can not figure out how to do
> this. Thank you!!!
>

[Rae Drysdale]

You can copy the cells and the use paste special and click on transpose at
the bottom of the dialogue box. You cannot overlap the paste, ie when you
want to paste A1, A2 and A3, you will have to click on B1 rather than A1. You
may need to insert columns to achieve this easily, and then delete the first
column when you are finished. Hope this helps.
--
Rae Drysdale


"Alan Beban" wrote:

> If the functions in the freely downloadable file at
> http//home.pacbell.net/beban are available to your workbook
>
> =ArrayReshape(A1:A300,100,3) array entered into B1:D100
>
> Alan Beban
>
> BCLivell wrote:
> > I have an excel worksheet that someone provide me. All of the information is
> > in 1 column (A) with each row having the person's contact info. The pattern
> > repeats for each contact. The pattern like this
> >
> > A1-Company name
> > A2-Name
> > A3-phone
> >
> > There are 100 contacts in this format. I need to seperate all of the
> > information so that the comany name is one column and the name is an another
> > column and the phone is in a third column. I can not figure out how to do
> > this. Thank you!!!
> >
>

[Biff]

Hi!

Try this:

Assume you want to transpose this data to a new sheet starting in cell A1:

Enter this formula in the new sheet cell A1:

=INDEX(Sheet1!$A:$A,(ROWS($1:1)-1)*3+COLUMN())

Copy across to C1 then down until you get returns of 0.

Biff

"BCLivell" <[email protected]> wrote in message
news:[email protected]...
>I have an excel worksheet that someone provide me. All of the information
>is
> in 1 column (A) with each row having the person's contact info. The
> pattern
> repeats for each contact. The pattern like this
>
> A1-Company name
> A2-Name
> A3-phone
>
> There are 100 contacts in this format. I need to seperate all of the
> information so that the comany name is one column and the name is an
> another
> column and the phone is in a third column. I can not figure out how to do
> this. Thank you!!!
>

[BCLivell]

Alan-

How d you incorperate the functions form your file?

"Alan Beban" wrote:

> If the functions in the freely downloadable file at
> http//home.pacbell.net/beban are available to your workbook
>
> =ArrayReshape(A1:A300,100,3) array entered into B1:D100
>
> Alan Beban
>
> BCLivell wrote:
> > I have an excel worksheet that someone provide me. All of the information is
> > in 1 column (A) with each row having the person's contact info. The pattern
> > repeats for each contact. The pattern like this
> >
> > A1-Company name
> > A2-Name
> > A3-phone
> >
> > There are 100 contacts in this format. I need to seperate all of the
> > information so that the comany name is one column and the name is an another
> > column and the phone is in a third column. I can not figure out how to do
> > this. Thank you!!!
> >
>

[simonsmith]

Creat a new column and put 1 in a1, 2 in a2, 3 in a3 and repeat by pasting down the column.Result you should have a 1 beside names a 2 beside phones numbers etc. Then in adjacent columns put in an IF formula to get your data out eg. =if(A1=1,A2,"") this could be in your B column and will provide you with your name. In Column C you could put =if(A1=2,A2,"") this will get the addresses etc. drag all your formulas down to you get all your data the way you want it then copy paste special , values to get rid of your formulas (if desired) Hope this helps

Simonsmith

[Alan Beban]

One way, after you have downloaded them into a file on your hard drive,
is to copy the file into your Personal.xls file.

Alan Beban

BCLivell wrote:
> Alan-
>
> How d you incorperate the functions form your file?
>
> "Alan Beban" wrote:
>
>> If the functions in the freely downloadable file at
>> http//home.pacbell.net/beban are available to your workbook
>>
>> =ArrayReshape(A1:A300,100,3) array entered into B1:D100
>>
>> Alan Beban
>>
>> BCLivell wrote:
>>> I have an excel worksheet that someone provide me. All of the information is
>>> in 1 column (A) with each row having the person's contact info. The pattern
>>> repeats for each contact. The pattern like this
>>>
>>> A1-Company name
>>> A2-Name
>>> A3-phone
>>>
>>> There are 100 contacts in this format. I need to seperate all of the
>>> information so that the comany name is one column and the name is an another
>>> column and the phone is in a third column. I can not figure out how to do
>>> this. Thank you!!!
>>>