MACRO HELP NEEDED - Move Row data into Columns

[coloradobu]

Hi - Thanks for this site, I have my addresses in a nice column:

1. LastName, FirstName
2. 123 Main Street
3. City, State, Zip USA
4.
5. LastName, FirstName
6. 345 South Street
7. City, State, Zip USA

Now the department wants the format changed to attributes per column:

1. Last Name | 123 Main Street | Apt/Lot/Unit | City | State | Zip
2. Last Name | 345 South Street | Apt/Lot/Unit | City | State | Zip


Is there a way to create a macro to transpose the data rather than me doing this one name group at a time?

Thanks in advance for your help! Need this ASAP, of course. <!-- / message -->

 

[VoG]

Maybe this - on a copy of your sheet

'addresses in column A, separated by spaces, to columns
Sub Addrs()
Dim Area As Range
For Each Area In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Area(1).Offset(, 1).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
Columns("A").Delete
Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[coloradobu]

Thank you - this works pretty well however is there a good way to use the 'text to column' feature to separate the address, apartment, city, state, zip?
there is quite a bit of cleanup to do still. The document has over 5500 rows. Technology is good..so happy you know how to use Excel! Thanks again

 

[HalfAce]

I believe you'll want to use Vog's (transpose) code as opposed to text to columns. (done manually or with code.)
Text to columns would leave you with:
123, Main and Street all in their own columns, not like what you've posted above.