Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple Columns to rows
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple Columns to rows
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 "Michelle" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple Columns to rows
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 -- Max Singapore --- "Michelle" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple Columns to rows
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) "pmartglass" wrote: 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 "Michelle" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple Columns to rows
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. -- Max Singapore --- "Michelle" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple Columns to rows
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 "Max" wrote: 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. -- Max Singapore --- "Michelle" wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple Columns to rows
Hi Max,
It works! Thank you so much!!!! "Max" wrote: 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. -- Max Singapore --- "Michelle" wrote: 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple Columns to rows
"Max" wrote: 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. -- Max Singapore --- "Michelle" wrote: 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple Columns to rows
welcome, good to hear
-- Max Singapore "Michelle" wrote in message ... Hi Max, It works! Thank you so much!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TRANSPOSE ROWS TO COLUMNS | Excel Discussion (Misc queries) | |||
Transpose columns to rows using first columns repeated. | Excel Worksheet Functions | |||
Transpose not working rows to columns | Excel Discussion (Misc queries) | |||
How do you transpose rows to columns? | Excel Discussion (Misc queries) | |||
how do I transpose columns and rows | Excel Discussion (Misc queries) |