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

 

[DanteAmor]

Assuming that your data starts in cell A2.
Execute this macro, the result will be in columns B, C, D and E

Sub test4()
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row Step 4
        Range("B" & Rows.Count).End(xlUp)(2).Resize(1, 4).Value = WorksheetFunction.Transpose(Range("A" & i).Resize(4, 1).Value)
    Next
End Sub

 

[anillinda]

The name changes sometimes in 8th row in colum and sometimes 9th. Is it possible to change the data

 

[DanteAmor]

The name changes sometimes in 8th row in colum and sometimes 9th. Is it possible to change the data

How do you identify when they are 8 or 9 or 4?

You can explain it with examples

 

[alansidman]

Using Power Query the following is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Column1"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"0", "3", "2"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([1] = "NAME")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

Result

v	A	B	C	D
1	1	2	3	0
2	NAME	DATE OF BIRTH	HIRE DATE	TERMINATION DATE
3	NAME	DATE OF BIRTH	HIRE DATE	TERMINATION DATE
4	NAME	DATE OF BIRTH	HIRE DATE	TERMINATION DATE

 

[anillinda]

Before the new name there is a cell that contains the text "Emp Type: Full time", after that the new name is in the data base: see this

DAVIS, BRYAN MICHAEL

5674 PEACE DRIVE

WEST CITY, CA 91543

Home Phone:

Mobile:

Salary Per Pay: 2658.00

Hourly: 23.4700

SSN: xxx-xx-6789

Hire Date: 3/28/2015

Birth Date: 10/25/1975

Department: 01 - CLERICAL

Status: Active

Emp Type: Full time

DAVID, BRYAN

6572 BELLYUP DR

WESTVALE, CA 91325

Home Phone:

Mobile: 9096987847

Hourly: 12.00

SSN: xxx-xx-7895

Hire Date: 8/8/2015

Birth Date: 5/20/1998

Department: 01 - CLERICAL

Status: Active

Emp Type: Full time

<colgroup><col></colgroup><tbody>
</tbody>

 

[anillinda]

How do I load the code??

 

[alansidman]

If you are referring to my post, then look at this link

https://excel.solutions/2017/11/power-query-paste-code-video/

 

[anillinda]

It did not work, my data is as follows

DAVIS, BRYAN MICHAEL

5674 PEACE DRIVE

WEST CITY, CA 91543

Home Phone:

Mobile:

Salary Per Pay: 2658.00

Hourly: 23.4700

SSN: xxx-xx-6789

Hire Date: 3/28/2015

Birth Date: 10/25/1975

Department: 01 - CLERICAL

Status: Active

Emp Type: Full time

DAVID, BRYAN

6572 BELLYUP DR

WESTVALE, CA 91325

Home Phone:

Mobile: 9096987847

Hourly: 12.00

SSN: xxx-xx-7895

Hire Date: 8/8/2015

Birth Date: 5/20/1998

Department: 01 - CLERICAL

Status: Active

Emp Type: Full time What I am trying to do is move the data axross the names in the row. Employee name changes every time when the cell above has the text Emp Type:?? Let me know if Power QUERY IS THE WAY TO GO. Thanks

<tbody>
</tbody>

 

[DanteAmor]

Before the new name there is a cell that contains the text "Emp Type: Full time", after that the new name is in the data base: see this

DAVIS, BRYAN MICHAEL

5674 PEACE DRIVE

WEST CITY, CA 91543

Home Phone:

Mobile:

Salary Per Pay: 2658.00

Hourly: 23.4700

SSN: xxx-xx-6789

Hire Date: 3/28/2015

Birth Date: 10/25/1975

Department: 01 - CLERICAL

Status: Active

Emp Type: Full time

DAVID, BRYAN

6572 BELLYUP DR

WESTVALE, CA 91325

Home Phone:

Mobile: 9096987847

Hourly: 12.00

SSN: xxx-xx-7895

Hire Date: 8/8/2015

Birth Date: 5/20/1998

Department: 01 - CLERICAL

Status: Active

Emp Type: Full time

<tbody>
</tbody>

Try this

Sub test4()
    Dim lr As Long, i As Long
    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