Hi there,

Look at the attached workbook example - i think this is what you are looking
for.
Here is the code:

Sub sort_names()

Dim lngLastRow As Long
Dim x As Integer
Dim lngOffsetCounter As Long
Dim arrName() As String
Dim arrTitle() As String
Dim arrFacility() As String
Dim arrStreet() As String
Dim arrCity() As String
Dim arrZip() As String
Dim arrPhone() As String

'find number of entries
With ActiveSheet
lngLastRow = .Range("A65536").End(xlUp).Row
End With

lngOffsetCounter = 4

ReDim arrName(lngLastRow) As String
ReDim arrTitle(lngLastRow) As String
ReDim arrFacility(lngLastRow) As String
ReDim arrStreet(lngLastRow) As String
ReDim arrCity(lngLastRow) As String
ReDim arrZip(lngLastRow) As String
ReDim arrPhone(lngLastRow) As String

For x = 0 To lngLastRow - 1
arrName(x) = Range("A" & x + 2).Value
arrTitle(x) = Range("B" & x + 2).Value
arrFacility(x) = Range("C" & x + 2).Value
arrStreet(x) = Range("D" & x + 2).Value
arrCity(x) = Range("E" & x + 2).Value
arrZip(x) = Range("F" & x + 2).Value
arrPhone(x) = Range("G" & x + 2).Value
Next x

Sheets("Sheet2").Select
For x = 0 To lngLastRow - 1
Range("A1").Offset((x * lngOffsetCounter) + 1, 0).Value = arrName(x)
Range("C1").Offset((x * lngOffsetCounter) + 1, 0).Value =
arrTitle(x)
Range("E1").Offset((x * lngOffsetCounter) + 1, 0).Value =
arrPhone(x)
Range("A2").Offset((x * lngOffsetCounter) + 1, 0).Value =
arrStreet(x)
Range("C2").Offset((x * lngOffsetCounter) + 1, 0).Value =
arrFacility(x)
Range("A3").Offset((x * lngOffsetCounter) + 1, 0).Value = arrCity(x)
Range("C3").Offset((x * lngOffsetCounter) + 1, 0).Value = arrZip(x)
Next x

End Sub

"smithbj" wrote in
message ...

I have an address spreadsheet that has approximately 7,000 addresses
that appears as follows:

__(A)___(B)_____(C)_____(D)______________(E)_____( F)___(G)
NAME_TITLE__FACILITY_STREET_______CITY/STATE__ZIP___PHONE #
John__Clerk____East___123 Main_______Atlanta, GA__05821__217-555-1111
Mary__Typist___West__2321 South St___Miami, FL___01251__654-555-4544
Fred___Director_South_847 12 Street____Kearny, NJ_98511___362-555-2313

I want to rearrange it to appear as follows:
__(A)_____________(B)____(C)_________(D)__________ (E)

John____________________Clerk____________________2 17-555-1111
123 Main________________ East
Atlanta, GA_______05821

Mary____________________Typist____________________ 654-555-4544
2321 South St____________West
Miami, FL__________01251

Fred____________________Director__________________ 362-555-2313
847 12 Street____________South
Kearny, NJ_________98511


Does anyone have any code that would programmatically rearrange the
spreadsheet as above. Any help would be appreciated.


--
smithbj
------------------------------------------------------------------------
smithbj's Profile:
http://www.excelforum.com/member.php...o&userid=36532
View this thread: http://www.excelforum.com/showthread...hreadid=562892