|
Rows to Columns on reference
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 |
Rows to Columns on reference
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 "excelmad" wrote in message ... 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 |
Rows to Columns on reference
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" wrote: 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 "excelmad" wrote in message ... 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 |
Rows to Columns on reference
I don't see where the INDIRECT function would have fit in with what you
have. Otto "excelmad" wrote in message ... 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" wrote: 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 "excelmad" wrote in message ... 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 |
Rows to Columns on reference
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 & 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. "Otto Moehrbach" wrote: 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 "excelmad" wrote in message ... 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 |
| All times are GMT +1. The time now is 04:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com