Column To Rows
 

I often get lists of contacts that are generated in one long column. They are
often may be 4 or 5 rows per record. In order to be able to use the data I
need to transpose the rows in each record (one column) to new columns. It may
include Company Name in Row A1 and contact name in Row B1 and perhaps Phone
or Web Site in C1 etc. The records are always the same amount of rows. I have
tried copy, transpose, and text to rows and anything else I can think of. Can
anyone make a suggestion ?? Thanks in advance Tony Pass

Column To Rows
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tony Pass" wrote in message
...
I often get lists of contacts that are generated in one long column. They
are
often may be 4 or 5 rows per record. In order to be able to use the data I
need to transpose the rows in each record (one column) to new columns. It
may
include Company Name in Row A1 and contact name in Row B1 and perhaps
Phone
or Web Site in C1 etc. The records are always the same amount of rows. I
have
tried copy, transpose, and text to rows and anything else I can think of.
Can
anyone make a suggestion ?? Thanks in advance Tony Pass

Column To Rows
 

If this line is true:
The records are always the same amount of rows ..
this OFFSET formula will also achieve the required transformation
Source data assumed continuous in A2 down, in groups of 3 lines each
In B2: =OFFSET($A$2,ROWS($1:1)*3-3+COLUMNS($A:A)-1,)
Copy across by 3 cols to D2, fill down to exhaust
Just change the 3 in the expression to suit the number of rows per group,
and copy across by the same number of cols before filling down to exhaust the
extract. Change the $A$2 to suit where your source data starts
Success? celebrate it, hit the YES below
--
Max
Singapore
---
"Tony Pass" wrote:
I often get lists of contacts that are generated in one long column. They are
often may be 4 or 5 rows per record. In order to be able to use the data I
need to transpose the rows in each record (one column) to new columns. It may
include Company Name in Row A1 and contact name in Row B1 and perhaps Phone
or Web Site in C1 etc. The records are always the same amount of rows. I have
tried copy, transpose, and text to rows and anything else I can think of. Can
anyone make a suggestion ?? Thanks in advance Tony Pass

Column To Rows
 

If you use formulas I recommend changing to values after.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Max" wrote in message
...
If this line is true:
The records are always the same amount of rows ..
this OFFSET formula will also achieve the required transformation
Source data assumed continuous in A2 down, in groups of 3 lines each
In B2: =OFFSET($A$2,ROWS($1:1)*3-3+COLUMNS($A:A)-1,)
Copy across by 3 cols to D2, fill down to exhaust
Just change the 3 in the expression to suit the number of rows per group,
and copy across by the same number of cols before filling down to exhaust
the
extract. Change the $A$2 to suit where your source data starts
Success? celebrate it, hit the YES below
--
Max
Singapore
---
"Tony Pass" wrote:
I often get lists of contacts that are generated in one long column. They
are
often may be 4 or 5 rows per record. In order to be able to use the data
I
need to transpose the rows in each record (one column) to new columns. It
may
include Company Name in Row A1 and contact name in Row B1 and perhaps
Phone
or Web Site in C1 etc. The records are always the same amount of rows. I
have
tried copy, transpose, and text to rows and anything else I can think of.
Can
anyone make a suggestion ?? Thanks in advance Tony Pass