software

I have a large file laid out as follows below.

Column A Column B
SSN1Name1
SSN1Address1
SSN1Address 2
SSN1City, State Zip
SSN2Name2
SSN2Address1
SSN2City, State Zip
SSN2
SSN3Name3
SSN3Address1
SSN3City, State Zip
SSN4Name4
SSN4Address1
SSN4Address 2
SSN4City, State Zip

How can I get this data to look like:

Column AColumn BColumn CColumn DColumn EColumn F
SSN #Full NameAddress Line 1Address Line 2Address Line 3City, State Zip
SSN1Name1Address1Address 2City, State Zip
SSN2Name2Address1City, State Zip
SSN3Name3Address1City, State Zip
SSN4Name4Address1Address 2City, State Zip
The macro below will do what you want.
In writing this macro I assumed the following:
Your data is in Columns A amp; 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(quot;A1quot;)
Do Until FirstCell.Value = quot;quot;
For c = 1 To 20
If FirstCell.Offset(c).Value lt;gt; FirstCell.Value Then
Set LastCell = FirstCell.Offset(c - 1)
Set Dest = Range(quot;Cquot; amp; 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(quot;A:Bquot;).Delete
MsgBox quot;Task has been completed.quot;
End Sub

quot;excelmadquot; gt; wrote in message
...
gt;I have a large file laid out as follows below.
gt;
gt; Column A Column B
gt; SSN1 Name1
gt; SSN1 Address1
gt; SSN1 Address 2
gt; SSN1 City, State Zip
gt; SSN2 Name2
gt; SSN2 Address1
gt; SSN2 City, State Zip
gt; SSN2
gt; SSN3 Name3
gt; SSN3 Address1
gt; SSN3 City, State Zip
gt; SSN4 Name4
gt; SSN4 Address1
gt; SSN4 Address 2
gt; SSN4 City, State Zip
gt;
gt; How can I get this data to look like:
gt;
gt; Column A Column B Column C Column D Column E Column F
gt; SSN # Full Name Address Line 1 Address Line 2 Address Line 3 City, State
gt; Zip
gt; SSN1 Name1 Address1 Address 2 City, State Zip
gt; SSN2 Name2 Address1 City, State Zip
gt; SSN3 Name3 Address1 City, State Zip
gt; SSN4 Name4 Address1 Address 2 City, State Zip
gt;
gt;
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.

quot;Otto Moehrbachquot; wrote:

gt; The macro below will do what you want.
gt; In writing this macro I assumed the following:
gt; Your data is in Columns A amp; B starting in A1.
gt; Columns C:H, in Row 1, have the headers you want in the final product, just
gt; as you listed them in your post.
gt; This macro will list all of your data as you wanted, in Columns C:H,
gt; underneath the headers. It will then delete Columns A:B.
gt; The final product is as you wanted and in Columns A:F.
gt; HTH Otto
gt;
gt; Sub ReArrange()
gt; Dim FirstCell As Range
gt; Dim LastCell As Range
gt; Dim Dest As Range
gt; Dim c As Long
gt; Set FirstCell = Range(quot;A1quot;)
gt; Do Until FirstCell.Value = quot;quot;
gt; For c = 1 To 20
gt; If FirstCell.Offset(c).Value lt;gt; FirstCell.Value Then
gt; Set LastCell = FirstCell.Offset(c - 1)
gt; Set Dest = Range(quot;Cquot; amp; Rows.Count).End(xlUp).Offset(1)
gt; Exit For
gt; End If
gt; Next c
gt; Dest.Value = FirstCell.Value
gt; For c = 1 To Range(FirstCell, LastCell).Count
gt; Dest.Offset(, c).Value = FirstCell.Offset(c - 1, 1).Value
gt; Next c
gt; Set FirstCell = LastCell.Offset(1)
gt; Loop
gt; Columns(quot;A:Bquot;).Delete
gt; MsgBox quot;Task has been completed.quot;
gt; End Sub
gt;
gt; quot;excelmadquot; gt; wrote in message
gt; ...
gt; gt;I have a large file laid out as follows below.
gt; gt;
gt; gt; Column A Column B
gt; gt; SSN1 Name1
gt; gt; SSN1 Address1
gt; gt; SSN1 Address 2
gt; gt; SSN1 City, State Zip
gt; gt; SSN2 Name2
gt; gt; SSN2 Address1
gt; gt; SSN2 City, State Zip
gt; gt; SSN2
gt; gt; SSN3 Name3
gt; gt; SSN3 Address1
gt; gt; SSN3 City, State Zip
gt; gt; SSN4 Name4
gt; gt; SSN4 Address1
gt; gt; SSN4 Address 2
gt; gt; SSN4 City, State Zip
gt; gt;
gt; gt; How can I get this data to look like:
gt; gt;
gt; gt; Column A Column B Column C Column D Column E Column F
gt; gt; SSN # Full Name Address Line 1 Address Line 2 Address Line 3 City, State
gt; gt; Zip
gt; gt; SSN1 Name1 Address1 Address 2 City, State Zip
gt; gt; SSN2 Name2 Address1 City, State Zip
gt; gt; SSN3 Name3 Address1 City, State Zip
gt; gt; SSN4 Name4 Address1 Address 2 City, State Zip
gt; gt;
gt; gt;
gt;
gt;
gt;

I don't see where the INDIRECT function would have fit in with what you
have. Otto
quot;excelmadquot; gt; wrote in message
...
gt; Thank you this worked perfectly. Would I have been able to use the
gt; INDIRECT
gt; function as well? I remember seeing it out here before but couldn't
gt; remember
gt; the exact formula.
gt;
gt; quot;Otto Moehrbachquot; wrote:
gt;
gt;gt; The macro below will do what you want.
gt;gt; In writing this macro I assumed the following:
gt;gt; Your data is in Columns A amp; B starting in A1.
gt;gt; Columns C:H, in Row 1, have the headers you want in the final product,
gt;gt; just
gt;gt; as you listed them in your post.
gt;gt; This macro will list all of your data as you wanted, in Columns C:H,
gt;gt; underneath the headers. It will then delete Columns A:B.
gt;gt; The final product is as you wanted and in Columns A:F.
gt;gt; HTH Otto
gt;gt;
gt;gt; Sub ReArrange()
gt;gt; Dim FirstCell As Range
gt;gt; Dim LastCell As Range
gt;gt; Dim Dest As Range
gt;gt; Dim c As Long
gt;gt; Set FirstCell = Range(quot;A1quot;)
gt;gt; Do Until FirstCell.Value = quot;quot;
gt;gt; For c = 1 To 20
gt;gt; If FirstCell.Offset(c).Value lt;gt; FirstCell.Value Then
gt;gt; Set LastCell = FirstCell.Offset(c - 1)
gt;gt; Set Dest = Range(quot;Cquot; amp; Rows.Count).End(xlUp).Offset(1)
gt;gt; Exit For
gt;gt; End If
gt;gt; Next c
gt;gt; Dest.Value = FirstCell.Value
gt;gt; For c = 1 To Range(FirstCell, LastCell).Count
gt;gt; Dest.Offset(, c).Value = FirstCell.Offset(c - 1, 1).Value
gt;gt; Next c
gt;gt; Set FirstCell = LastCell.Offset(1)
gt;gt; Loop
gt;gt; Columns(quot;A:Bquot;).Delete
gt;gt; MsgBox quot;Task has been completed.quot;
gt;gt; End Sub
gt;gt;
gt;gt; quot;excelmadquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have a large file laid out as follows below.
gt;gt; gt;
gt;gt; gt; Column A Column B
gt;gt; gt; SSN1 Name1
gt;gt; gt; SSN1 Address1
gt;gt; gt; SSN1 Address 2
gt;gt; gt; SSN1 City, State Zip
gt;gt; gt; SSN2 Name2
gt;gt; gt; SSN2 Address1
gt;gt; gt; SSN2 City, State Zip
gt;gt; gt; SSN2
gt;gt; gt; SSN3 Name3
gt;gt; gt; SSN3 Address1
gt;gt; gt; SSN3 City, State Zip
gt;gt; gt; SSN4 Name4
gt;gt; gt; SSN4 Address1
gt;gt; gt; SSN4 Address 2
gt;gt; gt; SSN4 City, State Zip
gt;gt; gt;
gt;gt; gt; How can I get this data to look like:
gt;gt; gt;
gt;gt; gt; Column A Column B Column C Column D Column E Column F
gt;gt; gt; SSN # Full Name Address Line 1 Address Line 2 Address Line 3 City,
gt;gt; gt; State
gt;gt; gt; Zip
gt;gt; gt; SSN1 Name1 Address1 Address 2 City, State Zip
gt;gt; gt; SSN2 Name2 Address1 City, State Zip
gt;gt; gt; SSN3 Name3 Address1 City, State Zip
gt;gt; gt; SSN4 Name4 Address1 Address 2 City, State Zip
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
Hello Otto

Is there a way to adjust this macro so that it looks for the first cell and
then the end of data or to assume data in Columns A:P starting in A1 instead
of Columns A amp; B starting in A1.

Also, (for future reference) what would I need to adjust to have the Macro
copy only certain columns instead of C:H in the example below

Any help is appreciated. Thank you.

quot;Otto Moehrbachquot; wrote:

gt; The macro below will do what you want.
gt; In writing this macro I assumed the following:
gt; Your data is in Columns A amp; B starting in A1.
gt; Columns C:H, in Row 1, have the headers you want in the final product, just
gt; as you listed them in your post.
gt; This macro will list all of your data as you wanted, in Columns C:H,
gt; underneath the headers. It will then delete Columns A:B.
gt; The final product is as you wanted and in Columns A:F.
gt; HTH Otto
gt;
gt; Sub ReArrange()
gt; Dim FirstCell As Range
gt; Dim LastCell As Range
gt; Dim Dest As Range
gt; Dim c As Long
gt; Set FirstCell = Range(quot;A1quot;)
gt; Do Until FirstCell.Value = quot;quot;
gt; For c = 1 To 20
gt; If FirstCell.Offset(c).Value lt;gt; FirstCell.Value Then
gt; Set LastCell = FirstCell.Offset(c - 1)
gt; Set Dest = Range(quot;Cquot; amp; Rows.Count).End(xlUp).Offset(1)
gt; Exit For
gt; End If
gt; Next c
gt; Dest.Value = FirstCell.Value
gt; For c = 1 To Range(FirstCell, LastCell).Count
gt; Dest.Offset(, c).Value = FirstCell.Offset(c - 1, 1).Value
gt; Next c
gt; Set FirstCell = LastCell.Offset(1)
gt; Loop
gt; Columns(quot;A:Bquot;).Delete
gt; MsgBox quot;Task has been completed.quot;
gt; End Sub
gt;
gt; quot;excelmadquot; gt; wrote in message
gt; ...
gt; gt;I have a large file laid out as follows below.
gt; gt;
gt; gt; Column A Column B
gt; gt; SSN1 Name1
gt; gt; SSN1 Address1
gt; gt; SSN1 Address 2
gt; gt; SSN1 City, State Zip
gt; gt; SSN2 Name2
gt; gt; SSN2 Address1
gt; gt; SSN2 City, State Zip
gt; gt; SSN2
gt; gt; SSN3 Name3
gt; gt; SSN3 Address1
gt; gt; SSN3 City, State Zip
gt; gt; SSN4 Name4
gt; gt; SSN4 Address1
gt; gt; SSN4 Address 2
gt; gt; SSN4 City, State Zip
gt; gt;
gt; gt; How can I get this data to look like:
gt; gt;
gt; gt; Column A Column B Column C Column D Column E Column F
gt; gt; SSN # Full Name Address Line 1 Address Line 2 Address Line 3 City, State
gt; gt; Zip
gt; gt; SSN1 Name1 Address1 Address 2 City, State Zip
gt; gt; SSN2 Name2 Address1 City, State Zip
gt; gt; SSN3 Name3 Address1 City, State Zip
gt; gt; SSN4 Name4 Address1 Address 2 City, State Zip
gt; gt;
gt; gt;
gt;
gt;
gt;