Convert rows to Columns

[Ed]

I have a repetitive text file of contact information and want to place the
data in columns.
The file looks like this:
company
address
city
phone fax
email

company
address a nd etc...

I want the data go to respective colmns.
Help

 

[Duke Carey]

The task varies in complexity depending on whether there is a consistent # of
rows for each contact or not. Your example shows 5 rows. Are they all 5?
If so, in cell B1 use this formula & copy across to F1, then copy it down as
far as needs be:

=OFFSET($A$1,(ROW()-1)*5+COLUMN()-2,0)

 

[Ed]

It did not work for me.
The file is set like this:

A + XXXXXX.
Clifford XXX
PO Box 7028
XXX, NC 28241-7028 US
Phone: (XXX) XXX-XXXX
Fax: (704) XXX-XXXX
Email: (e-mail address removed)
Web: www.XXXXX.com

2 line row spacers
repeat again same 8 rows of new data

Hope you can figure this one out.
tks

 

[Duke Carey]

OK, assuming your data starts in A1, then use this in the first row in
B1:I1

=OFFSET($A$1,(ROW()-1)*8+COLUMN()-2,0)

and this for all subsequent rows

=OFFSET($A$1,(ROW()-1)*8+COLUMN(),0)

 

[Duke Carey]

Well, that doesn't work for rows 3 on.

Kinda goofy, but it appears to work for all the rows:

=OFFSET($A$1,(ROW()-1)*8+COLUMN()+2*(ROW()-2),0)

 

[Gord Dibben]

Select B1 and paste this in.

=INDEX($A:$A,(ROWS($1:1)-1)*9+COLUMNS($A:B)-1)

Copy across to J1

Copy B1:J1 down until zeros show up.

Delete Column J when done.

Alternative...............

Before taking these steps select Column A and F5>Special>Blanks>OK

Edit>Delete>Entire row.

Now just copy formula over to column I


Gord Dibben MS Excel MVP

 

[Ed]

It just wont work- there has to be way to do this- wish i could figure it out-

it workfor the first 3 lines- rows and then the data just wont align itself
in the colums.

any other ideas???

..

 

[Gord Dibben]

Ed

Did you read my post?

I will repeat here................

Select B1 and paste this in.

=INDEX($A:$A,(ROWS($1:1)-1)*9+COLUMNS($A:B)-1)

Copy across to J1

Copy B1:J1 down until zeros show up.

Delete Column J when done.

Alternative...............

Before taking these steps select Column A and F5>Special>Blanks>OK

Edit>Delete>Entire row.

Now just copy formula over to column I


Gord Dibben MS Excel MVP