Option Explicit
Sub ReorgData()
' hiker95, 11/07/2012
' http://www.mrexcel.com/forum/excel-questions/668372-change-every-group-rows-columns.html
Dim r As Long, lr As Long, nr As Long, s, t
Application.ScreenUpdating = False
Columns("C:I").ClearContents
Cells(1, 3).Resize(, 7).Value = [{"Name","Address","City","State","Zip","Phone","Fax"}]
lr = Cells(Rows.Count, 1).End(xlUp).Row
nr = 1
For r = 1 To lr Step 6
nr = nr + 1
Cells(nr, 3).Resize(, 2).Value = Application.Transpose(Cells(r, 1).Resize(2).Value)
s = Split(Cells(r + 2, 1), ", ")
Cells(nr, 5).Value = s(0)
t = Split(s(1), " ")
Cells(nr, 6).Resize(, 2).Value = t
Cells(nr, 8) = Right(Cells(r + 3, 1), Len(Cells(r + 3, 1)) - 6)
Cells(nr, 9) = Right(Cells(r + 4, 1), Len(Cells(r + 4, 1)) - 4)
Next r
Columns("C:I").AutoFit
Application.ScreenUpdating = True
End Sub