Transpose multiple Columns to rows

[Michelle]

Hi,

I know if I have few columns I can transpose it to rows by using the
function "Transpose". But if I have hundreds of columns that content the
same information, how can I transfer them to Rows. Please see the sample
below.

Name MICRO ABRASIVES CORP.
City WESTFIELD
Address 720 SOUTHAMPTON RD., PO BOX 669
Zip Code 01086-0669
Region MA
Tel (413) 562-3641-
Fax (413) 562-7409-

Name FINISHING SOLUTIONS INC.
City WESTFIELD
Address 103 SERVISTAR INDUSTRIAL WAY
Zip Code 1085
Region MA
Tel (413) 568-3765-
Fax (413) 572-4088-

Name O S WALKER COMPANY
City WORCESTER
Address 20 ROCKDALE STREET
Zip Code 01606-1995
Region MA
Tel (508) 853-3232-
Fax (508) 852-8649-

Thanks,
Michelle

 

[pmartglass]

Would you consider adding a page to do this ?
either way I think the easiest way would be to use index
assuming your data is in column B
and you have your titles in row 1 and are starting the transpose in row 2

your formula would look something like this for the name
=INDEX(Sheet1!B:B,(ROW(A2)-2)*7+1)
city would look something like
=INDEX(Sheet1!B:B,(ROW(A2)-2)*7+2)
and so on

then you can copy down for as many rows as you need

hope this helps

 

[Max]

Assume your data as posted is in Sheet1, cols A and B from row1 down, where
each data group is 8 lines (7 lines + 1 blank line). Col A = headers, Col B =
individual data

In another sheet,
Copy n Paste this into any starting cell, say in B2:
=INDEX(Sheet1!$B:$B,ROWS($1:1)*8-8+COLUMNS($A:A))
Copy B2 across by 7 cols to H2, fill down as far as required to exhaust
Success? hit YES below

 

[Michelle]

Is there a way I can use the formular reference for "-2" and "+2". I have
over a thousand lines. I am trying to avoid manually change the formular for
each rows.

INDEX(Sheet1!B:B,(ROW(A2)-2)*7+2)

 

[Max]

Try the option in my response in the other branch. It gives you the point
formula to paste into a startcell of your choice, then a smooth drag across n
fill down all the way to return the desired results in a matter of seconds.

 

[Michelle]

Hi Max,

I tried your formular. It works for the first row, when I drag down to make
the copy of the formular, it shows the same information as the first row,
like this:

Name City Address Zip Code Region Tel Fax
Name City Address Zip Code Region Tel Fax
Name City Address Zip Code Region Tel Fax

Thanks,
Michelle

 

[Michelle]

Hi Max,

It works! Thank you so much!!!!

 

[Michelle]

 

[Max]

welcome, good to hear