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

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!

The easiest method is to in B1
enter
=A2
copy down and across to end of data column D

Select columns A:D
copy and paste special values
in C1 enter
= mod(row(),5)
Select column E
<data<Filter<Autofilter)
on the autofilter select arrow select custom < 1
select all of the visible rows except 1
and <edit<Delete row

It is easier to do than it is to write.
"zooeyhallne" wrote:

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

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!

Hi, I wonder how you can place 1000 names in the row cause Excel only has 256
column in a row.

Well, the easiest way to do that is to Copy and Paste Special (choose
"transpose")

Hope it helps.

Hank


"bj" wrote:

The easiest method is to in B1
enter
=A2
copy down and across to end of data column D

Select columns A:D
copy and paste special values
in C1 enter
= mod(row(),5)
Select column E
<data<Filter<Autofilter)
on the autofilter select arrow select custom < 1
select all of the visible rows except 1
and <edit<Delete row

It is easier to do than it is to write.
"zooeyhallne" wrote:

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

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!

Actually, if you read my post it says that I want the info for each customer
on its own row.

"Hank" wrote:

Hi, I wonder how you can place 1000 names in the row cause Excel only has 256
column in a row.

Well, the easiest way to do that is to Copy and Paste Special (choose
"transpose")

Hope it helps.

Hank


"bj" wrote:

The easiest method is to in B1
enter
=A2
copy down and across to end of data column D

Select columns A:D
copy and paste special values
in C1 enter
= mod(row(),5)
Select column E
<data<Filter<Autofilter)
on the autofilter select arrow select custom < 1
select all of the visible rows except 1
and <edit<Delete row

It is easier to do than it is to write.
"zooeyhallne" wrote:

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

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!

does my suggestion not do this?

"zooeyhallne" wrote:

Actually, if you read my post it says that I want the info for each customer
on its own row.

"Hank" wrote:

Hi, I wonder how you can place 1000 names in the row cause Excel only has 256
column in a row.

Well, the easiest way to do that is to Copy and Paste Special (choose
"transpose")

Hope it helps.

Hank


"bj" wrote:

The easiest method is to in B1
enter
=A2
copy down and across to end of data column D

Select columns A:D
copy and paste special values
in C1 enter
= mod(row(),5)
Select column E
<data<Filter<Autofilter)
on the autofilter select arrow select custom < 1
select all of the visible rows except 1
and <edit<Delete row

It is easier to do than it is to write.
"zooeyhallne" wrote:

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

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!

zooey

=INDEX($A:$A,(ROWS($1:1)-1)*5+COLUMNS($A:B)-1)

Enter this is B1 and drag across to E1.

Select B1:E1 and drag down until you get 0's showing up.


Gord Dibben MS Excel MVP

On Thu, 15 Feb 2007 09:23:54 -0800, zooeyhallne
wrote:

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

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!