Column To Rows

  • Thread starter Thread starter Tony Pass
  • Start date Start date
T

Tony Pass

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
 
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.
 
If this line is true:
The records are always the same amount of rows ..
Click to expand...
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
 
Back
Top