Worksheet formatting stumper!! (repost)
 

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

Worksheet formatting stumper!! (repost)
 

On Feb 15, 12:56 pm, zooeyhallne
wrote:
(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.

Worksheet formatting stumper!! (repost)
 

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.
"zooeyhallne" wrote:

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

Worksheet formatting stumper!! (repost)
 

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

On Thu, 15 Feb 2007 09:56:17 -0800, zooeyhallne
wrote:

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