Rows to Columns on reference

[Guest]

I have a large file laid out as follows below.

Column A Column B
SSN1 Name1
SSN1 Address1
SSN1 Address 2
SSN1 City, State Zip
SSN2 Name2
SSN2 Address1
SSN2 City, State Zip
SSN2
SSN3 Name3
SSN3 Address1
SSN3 City, State Zip
SSN4 Name4
SSN4 Address1
SSN4 Address 2
SSN4 City, State Zip

How can I get this data to look like:

Column A Column B Column C Column D Column E Column F
SSN # Full Name Address Line 1 Address Line 2 Address Line 3 City, State Zip
SSN1 Name1 Address1 Address 2 City, State Zip
SSN2 Name2 Address1 City, State Zip
SSN3 Name3 Address1 City, State Zip
SSN4 Name4 Address1 Address 2 City, State Zip

 

[Otto Moehrbach]

The macro below will do what you want.
In writing this macro I assumed the following:
Your data is in Columns A & B starting in A1.
Columns C:H, in Row 1, have the headers you want in the final product, just
as you listed them in your post.
This macro will list all of your data as you wanted, in Columns C:H,
underneath the headers. It will then delete Columns A:B.
The final product is as you wanted and in Columns A:F.
HTH Otto

Sub ReArrange()
Dim FirstCell As Range
Dim LastCell As Range
Dim Dest As Range
Dim c As Long
Set FirstCell = Range("A1")
Do Until FirstCell.Value = ""
For c = 1 To 20
If FirstCell.Offset(c).Value <> FirstCell.Value Then
Set LastCell = FirstCell.Offset(c - 1)
Set Dest = Range("C" & Rows.Count).End(xlUp).Offset(1)
Exit For
End If
Next c
Dest.Value = FirstCell.Value
For c = 1 To Range(FirstCell, LastCell).Count
Dest.Offset(, c).Value = FirstCell.Offset(c - 1, 1).Value
Next c
Set FirstCell = LastCell.Offset(1)
Loop
Columns("A:B").Delete
MsgBox "Task has been completed."
End Sub

 

[Guest]

Thank you this worked perfectly. Would I have been able to use the INDIRECT
function as well? I remember seeing it out here before but couldn't remember
the exact formula.

 

[Otto Moehrbach]

I don't see where the INDIRECT function would have fit in with what you
have. Otto