Worksheet formatting stumper!! (repost)

[Guest]

(I am reposting this message to clarify some things in the original post).

Hi everyone! I am hoping someone can give me answer on formatting data on an
Excel spreadsheet. I am generally knowledgable in Excel, but this one has
got me stumped.
I have been given an Excel spreadsheet with a list of our company's
customers. The data is currently in the following format on the sheet
(numbers in parenthesis are cell reference numbers):

(A1) <customer name #1>
(A2) <business name>
(A3) <street address>
(A4) <city, state, zipcode>
(A5) <blank line>
(A6) <customer name #2>
(A7) <business name>
(A8) <street address>
(A9) <city, state, zipcode>
(A10) <blank line>

etc.....

I need to change the sheet so that information for each customer is all in a
row, for example:

(A1) <customer name#1> (B1) <business name> (C1) <address> (D1) <city,
state, zip>
(A2) <customer name #2> (B2) <business name> (C2) <address> (D2) <city,
state, zipcode>

etc.....

There are about 1000 names on this sheet. Is there an easy way I can do
this? Thank you in advance! I truly appreciate any help on this!

 

[patrickkillian]

(I am reposting this message to clarify some things in the original post).

Hi everyone! I am hoping someone can give me answer on formatting data on an
Excel spreadsheet. I am generally knowledgable in Excel, but this one has
got me stumped.
I have been given an Excel spreadsheet with a list of our company's
customers. The data is currently in the following format on the sheet
(numbers in parenthesis are cell reference numbers):

(A1) <customer name #1>
(A2) <business name>
(A3) <street address>
(A4) <city, state, zipcode>
(A5) <blank line>
(A6) <customer name #2>
(A7) <business name>
(A8) <street address>
(A9) <city, state, zipcode>
(A10) <blank line>

etc.....

I need to change the sheet so that information for each customer is all in a
row, for example:

(A1) <customer name#1> (B1) <business name> (C1) <address> (D1) <city,
state, zip>
(A2) <customer name #2> (B2) <business name> (C2) <address> (D2) <city,
state, zipcode>

etc.....

There are about 1000 names on this sheet. Is there an easy way I can do
this? Thank you in advance! I truly appreciate any help on this!

One fast way to do this is create a pivot table out of the data. then
you can move the categories from row heading to column heading, and
then copy/paste all the data into a new worksheet.

 

[Guest]

Make a copy of the sheet to test this with. Add this code as a macro and run
it while your test sheet is selected/active.

Sub RotateDataToRows()
Dim LastRow As Long
Dim COffset As Integer
Dim ROffset1 As Long
Dim ROffset2 As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1").Select
ROffset1 = 0
ROffset2 = 0
Do Until ROffset2 > LastRow
For COffset = 0 To 4
ActiveCell.Offset(ROffset1, COffset) = _
ActiveCell.Offset(ROffset2, 0)
If ROffset2 > 1 Then
ActiveCell.Offset(ROffset2, 0) = ""
End If
ROffset2 = ROffset2 + 1
Next
ROffset1 = ROffset1 + 1
Loop
End Sub

If you're unsure of how to get the code into a standard code module, please
see this page: http://www.jlathamsite.com/Teach/Excel_GP_Code.htm
where the process is laid out in very gruesome detail if you need that level
of help with it. Just cut the above code and paste into a standard code
module and run it as you would any macro.

 

[Gord Dibben]

See your other post.

Changing to a new thread does very little to get you more answers.

Your needs were spelled quite well in your first post.

See my response there.


Gord Dibben MS Excel MVP