Convert rows to Columns
 

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

Convert rows to Columns
 

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" wrote:

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

Convert rows to Columns
 

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:
Web: www.XXXXX.com

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

Hope you can figure this one out.
tks
"Ed" wrote:

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

Convert rows to Columns
 

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)


"Ed" wrote:

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:
Web: www.XXXXX.com

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

Hope you can figure this one out.
tks
"Ed" wrote:

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

Convert rows to Columns
 

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)


"Duke Carey" wrote:

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)


"Ed" wrote:

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:
Web: www.XXXXX.com

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

Hope you can figure this one out.
tks
"Ed" wrote:

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

Convert rows to Columns
 

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 F5SpecialBlanksOK

EditDeleteEntire row.

Now just copy formula over to column I


Gord Dibben MS Excel MVP

On Thu, 3 Aug 2006 09:29:02 -0700, Ed wrote:

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:
Web: www.XXXXX.com

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

Hope you can figure this one out.
tks
"Ed" wrote:

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

Convert rows to Columns
 

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???

..


"Duke Carey" wrote:

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)


"Duke Carey" wrote:

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)


"Ed" wrote:

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:
Web: www.XXXXX.com

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

Hope you can figure this one out.
tks
"Ed" wrote:

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

Convert rows to Columns
 

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 F5SpecialBlanksOK

EditDeleteEntire row.

Now just copy formula over to column I


Gord Dibben MS Excel MVP

On Thu, 3 Aug 2006 13:35:02 -0700, Ed wrote:

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???

.


"Duke Carey" wrote:

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)


"Duke Carey" wrote:

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)


"Ed" wrote:

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:
Web: www.XXXXX.com

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

Hope you can figure this one out.
tks
"Ed" wrote:

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