Worksheet formatting stumper!

[Guest]

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!

 

[Guest]

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

Select columns A
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.

 

[Guest]

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

 

[Guest]

Actually, if you read my post it says that I want the info for each customer

 

[Guest]

does my suggestion not do this?

Actually, if you read my post it says that I want the info for each customer

 

[Gord Dibben]

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