Sorting Data from a Single Column

[Wendy Lauerman]

I have a column of information such as....

Name
Address
City
State
Zip
Name
Address
City
State
Zip

...and I would like to sort it into columns. 1st column would contain
all names, 2nd, all addresses, 3rd all cities....etc.

Since the column is quite long with 1000 names and addresses I would
like to know if there is a quick way to get Excel to sort this data.

[Alan Beban]

If the functions in the file at http://home.pacbell.net/beban are
available to your workbook, one way is to highlight a range of 5 columns
and 1/5th the number of rows of data in your data range (assumed to be
a1:a100 in this example) and array enter (enter with Ctrl+Shift+Enter
instead of just Enter)

=ArrayReshape(A1:A100,20,5)

Alan Beban

[John Walkenbach]

One approach is to use the 'Transform Vertical Range' utility in my PUP 2000
add-in. It is designed to do exactly what you describe. The add-in is
available at my web site (30-day free trial).

You can also accomplish this using formulas. Someone will probably post the
details.

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"Wendy Lauerman" <wendy.l...@tx.usda.gov> wrote in message
news:3B77DEB1...@tx.usda.gov...

[Otto Moehrbach]

Hi Wendy
The following macro should do what you want. I assumed your original
data was in column "A" starting in cell "A1". The final product starts in
row 1 in column "B". You can change these things as you wish to suit your
data. The macro will stop itself when it runs out of data in column "A".
Otto

Sub ReArrange()
Application.ScreenUpdating = False
For Counter = 1 To 5000
Range("A1").Select
If ActiveCell.Offset((Counter - 1) * 5, 0).Value = _
"" Then Exit For
ActiveCell.Offset((Counter - 1) * 5, 0). _
Range("A1:A5").Copy
ActiveCell.Offset(Counter - 1, 1). _
PasteSpecial Transpose:=True
Next
End Sub

"Wendy Lauerman" <wendy.l...@tx.usda.gov> wrote in message
news:3B77DEB1...@tx.usda.gov...