Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rotating Rows To Columns
I have some data that was sent to me in a linear fashion with what should
have been the Column headers as row headers. So basically every five rows, it repeats the same information for the contact - basicallyin column A, First Name, Last Name, Company, E-mail, and Phone Number, then in repeats every 5 cells. Column B has the information on the contact there. How can I get the information to rotate, so that I only have one entry for Column Headers across the top and the data directly underneath? Thanks in advance, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rotating Rows To Columns
Hi Tripp,
Assuming your data are in column A on Sheet1, insert the following formula in cell A1 on another worksheet and copy across to column E, then down as far as needed: =OFFSET(Sheet1!$A$1,COLUMN()-1+(ROW()-1)*5,INT((ROW()-1)/5)) If your existing data has empty cells, the above formula will return 0s, which you can hide (Tools|Options|View and uncheck 'zero values'). If you need to suppress 0s instead, the formula becomes: =IF(OFFSET(Sheet1!$A$1,COLUMN()-1+(ROW()-1)*5,INT((ROW()-1)/5))=0,"",OFFSET(Sheet1!$A$1,COLUMN()-1+(ROW()-1)*5,INT((ROW()-1)/5))) Cheers -- macropod [MVP - Microsoft Word] "Tripp K" wrote in message ... | I have some data that was sent to me in a linear fashion with what should | have been the Column headers as row headers. So basically every five rows, | it repeats the same information for the contact - basicallyin column A, First | Name, Last Name, Company, E-mail, and Phone Number, then in repeats every 5 | cells. Column B has the information on the contact there. | | How can I get the information to rotate, so that I only have one entry for | Column Headers across the top and the data directly underneath? | | Thanks in advance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rows & Columns in Excel | Excel Worksheet Functions | |||
convert columns to rows & rows to columns | Excel Discussion (Misc queries) | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
hidden rows & columns slow file open | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) |