How do I rotate data in a spreadsheet so that rows become columns?

[Guest]

Once again I find myself needing to do something in Excel that I know SHOULD
be possible, but have never figured out HOW. I've captured meaningful data as
text and pasted it in Excel. First row is "name", second row is "address",
third row is "city", fourth row is blank and then the fifth row starts with
the next record's "name". My need and desire is to rotate this data so that
column one is "name", column two is "address", column three is "city" and
each row is a separate record.

Assistance please!
Thanks!!

 

[Don Guillett]

Have a look in help index for TRANSPOSE

 

[sonicblue]

Copy - Paste Special - Tranpos

 

[Guest]

Hello

Select the area with all the data you want to rotate
Copy (Crtl+C)
Paste special into a new cell , and mark the "transpose" option

Kind regards Marcus

 

[Gord Dibben]

Dennis

Copy and Transpose becomes quite tedious when you have a thousand sets.

This macro will do the trick.

Enter 4 in "number of columns" inputbox to account for the blank rows.

Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP