Data from Rows into columns

G

Guest

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!!!
 
A

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
 
G

Guest

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.
 
G

Guest

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.
 
B

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
 
S

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top