split data across columns if the data is in one column only

[anillinda]

My data is in one column as follows:
NAME
DATE OF BIRTH
HIRE DATE
TERMINATION DATE
NAME
DATE OF BIRTH
HIRE DATE
TERMINATION DATE
NAME
DATE OF BIRTH
HIRE DATE
TERMINATION DATE

I need the name in one column and the date of birth in next column followed with hire date and termination date. How can I do this? I have over 3000 records.
Thanks
Neal

 

[anillinda]

says variable not defined and highlights f=

 

[anillinda]

compile error: variable not defined

 

[DanteAmor]

compile error: variable not defined

All that remained was to declare the variable f

Sub test4()
    Dim lr As Long, i As Long, [COLOR=#0000ff]f as range[/COLOR]
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        Set f = Range("A" & i & ":A" & lr).Find("Emp Type: Full time", LookIn:=xlValues, lookat:=xlPart)
        If Not f Is Nothing Then
            Range("B" & Rows.Count).End(xlUp)(2).Resize(1, f.Row - i).Value = WorksheetFunction.Transpose(Range("A" & i, Cells(f.Row, "A")).Value)
            i = f.Row
        End If
    Next
End Sub

 

[alansidman]

Here is a link to a file generated based upon your data that I have transformed using Power Query. Note that because your data is inconsistent in the number of records per person there is a slight hiccup in the transformation. This can be resolved using the same number of records per person. It appears that there is a salary field for one person but not the other.

https://app.box.com/s/ddz4ey8ond3uhu3zq1et5gays946m82v