Moving data to new columns

[siesawhawk]

I have one column with repeating name, address, city/state/zip, and phone number for locations, each in it's own row. For example, A1 is name 1, A2 is address 1, A3 is City/state/zip 1, and A4 is phone 1. Then A5 is name 2, A6 is address 2, etc. This repeats down column 1 for several hundred times. I want to end up with a column for Name, a column for address, a column for city/state/zip, and a column for phone.

I know this should be easy, but I'm a dummy. Thanks for any help!

 

[VoG]

Hello and welcome to MrExcel.

Are there always 4 rows per entry or is there anything that indicates when an entry starts?

 

[siesawhawk]

In this instance, 4 rows per entry. There are no commas or other separators.

 

[VoG]

Try this. Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste the following into the white space on the right:

Sub Xpose()
Dim LR As Long, i As Long, j As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add
With ws1
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR Step 4
        j = j + 1
        .Range("A" & i).Resize(4).Copy
        ws2.Range("A" & j).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Next i
End With
End Sub

Press ALT + Q to return to your sheet, Tools > Macro > Macros, click on Xpose then click the Run button.

 

[siesawhawk]

I couldn't get back in yesterday afternoon for some reason, but it worked perfectly. THANK YOU!