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

Have a look in help index for TRANSPOSE

--
Don Guillett
SalesAid Software

"Dennis_Kesselring" wrote in
message ...
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!!

Copy - Paste Special - Tranpose


--
sonicblue
------------------------------------------------------------------------
sonicblue's Profile: http://www.excelforum.com/member.php...o&userid=28990
View this thread: http://www.excelforum.com/showthread...hreadid=487192

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

"Dennis_Kesselring" wrote:

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

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

On Tue, 22 Nov 2005 05:35:05 -0800, "Dennis_Kesselring"
wrote:

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