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,
|