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

A

anillinda

New Member
Joined
Apr 20, 2012
Messages
48
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Assuming that your data starts in cell A2.
Execute this macro, the result will be in columns B, C, D and E


Code: 
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
 
Upvote 0
Using Power Query the following is the Mcode
Code: 
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
[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]0[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]NAME[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]DATE OF BIRTH[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]HIRE DATE[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]TERMINATION DATE[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]NAME[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]DATE OF BIRTH[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]HIRE DATE[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]TERMINATION DATE[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]NAME[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]DATE OF BIRTH[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]HIRE DATE[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]TERMINATION DATE[/td]
[/tr]
[/table]
 
Upvote 0
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
[TABLE="width: 247"]
<colgroup><col></colgroup><tbody>[TR]
[TD]DAVIS, BRYAN MICHAEL[/TD]
[/TR]
[TR]
[TD]5674 PEACE DRIVE[/TD]
[/TR]
[TR]
[TD]WEST CITY, CA 91543[/TD]
[/TR]
[TR]
[TD]Home Phone:[/TD]
[/TR]
[TR]
[TD]Mobile:[/TD]
[/TR]
[TR]
[TD]Salary Per Pay: 2658.00[/TD]
[/TR]
[TR]
[TD]Hourly: 23.4700[/TD]
[/TR]
[TR]
[TD]SSN: xxx-xx-6789[/TD]
[/TR]
[TR]
[TD]Hire Date: 3/28/2015[/TD]
[/TR]
[TR]
[TD]Birth Date: 10/25/1975[/TD]
[/TR]
[TR]
[TD]Department: 01 - CLERICAL[/TD]
[/TR]
[TR]
[TD]Status: Active[/TD]
[/TR]
[TR]
[TD]Emp Type: Full time[/TD]
[/TR]
[TR]
[TD]DAVID, BRYAN[/TD]
[/TR]
[TR]
[TD]6572 BELLYUP DR[/TD]
[/TR]
[TR]
[TD]WESTVALE, CA 91325[/TD]
[/TR]
[TR]
[TD]Home Phone:[/TD]
[/TR]
[TR]
[TD]Mobile: 9096987847[/TD]
[/TR]
[TR]
[TD]Hourly: 12.00[/TD]
[/TR]
[TR]
[TD]SSN: xxx-xx-7895[/TD]
[/TR]
[TR]
[TD]Hire Date: 8/8/2015[/TD]
[/TR]
[TR]
[TD]Birth Date: 5/20/1998[/TD]
[/TR]
[TR]
[TD]Department: 01 - CLERICAL[/TD]
[/TR]
[TR]
[TD]Status: Active[/TD]
[/TR]
[TR]
[TD]Emp Type: Full time[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
It did not work, my data is as follows [TABLE="class: cms_table, width: 247"]
<tbody>[TR]
[TD]DAVIS, BRYAN MICHAEL[/TD]
[/TR]
[TR]
[TD]5674 PEACE DRIVE[/TD]
[/TR]
[TR]
[TD]WEST CITY, CA 91543[/TD]
[/TR]
[TR]
[TD]Home Phone:[/TD]
[/TR]
[TR]
[TD]Mobile:[/TD]
[/TR]
[TR]
[TD]Salary Per Pay: 2658.00[/TD]
[/TR]
[TR]
[TD]Hourly: 23.4700[/TD]
[/TR]
[TR]
[TD]SSN: xxx-xx-6789[/TD]
[/TR]
[TR]
[TD]Hire Date: 3/28/2015[/TD]
[/TR]
[TR]
[TD]Birth Date: 10/25/1975[/TD]
[/TR]
[TR]
[TD]Department: 01 - CLERICAL[/TD]
[/TR]
[TR]
[TD]Status: Active[/TD]
[/TR]
[TR]
[TD]Emp Type: Full time[/TD]
[/TR]
[TR]
[TD]DAVID, BRYAN[/TD]
[/TR]
[TR]
[TD]6572 BELLYUP DR[/TD]
[/TR]
[TR]
[TD]WESTVALE, CA 91325[/TD]
[/TR]
[TR]
[TD]Home Phone:[/TD]
[/TR]
[TR]
[TD]Mobile: 9096987847[/TD]
[/TR]
[TR]
[TD]Hourly: 12.00[/TD]
[/TR]
[TR]
[TD]SSN: xxx-xx-7895[/TD]
[/TR]
[TR]
[TD]Hire Date: 8/8/2015[/TD]
[/TR]
[TR]
[TD]Birth Date: 5/20/1998[/TD]
[/TR]
[TR]
[TD]Department: 01 - CLERICAL[/TD]
[/TR]
[TR]
[TD]Status: Active[/TD]
[/TR]
[TR]
[TD]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[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
anillinda said:
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
[TABLE="width: 247"]
<tbody>[TR]
[TD]DAVIS, BRYAN MICHAEL[/TD]
[/TR]
[TR]
[TD]5674 PEACE DRIVE[/TD]
[/TR]
[TR]
[TD]WEST CITY, CA 91543[/TD]
[/TR]
[TR]
[TD]Home Phone:[/TD]
[/TR]
[TR]
[TD]Mobile:[/TD]
[/TR]
[TR]
[TD]Salary Per Pay: 2658.00[/TD]
[/TR]
[TR]
[TD]Hourly: 23.4700[/TD]
[/TR]
[TR]
[TD]SSN: xxx-xx-6789[/TD]
[/TR]
[TR]
[TD]Hire Date: 3/28/2015[/TD]
[/TR]
[TR]
[TD]Birth Date: 10/25/1975[/TD]
[/TR]
[TR]
[TD]Department: 01 - CLERICAL[/TD]
[/TR]
[TR]
[TD]Status: Active[/TD]
[/TR]
[TR]
[TD]Emp Type: Full time[/TD]
[/TR]
[TR]
[TD]DAVID, BRYAN[/TD]
[/TR]
[TR]
[TD]6572 BELLYUP DR[/TD]
[/TR]
[TR]
[TD]WESTVALE, CA 91325[/TD]
[/TR]
[TR]
[TD]Home Phone:[/TD]
[/TR]
[TR]
[TD]Mobile: 9096987847[/TD]
[/TR]
[TR]
[TD]Hourly: 12.00[/TD]
[/TR]
[TR]
[TD]SSN: xxx-xx-7895[/TD]
[/TR]
[TR]
[TD]Hire Date: 8/8/2015[/TD]
[/TR]
[TR]
[TD]Birth Date: 5/20/1998[/TD]
[/TR]
[TR]
[TD]Department: 01 - CLERICAL[/TD]
[/TR]
[TR]
[TD]Status: Active[/TD]
[/TR]
[TR]
[TD]Emp Type: Full time[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Try this
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Populating different sheets based on the value in a column on another sheet.
Replies
3
Views
792
DanteAmor
DanteAmor
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
371
kbishop94
K
N
  • Question Question
Excel cell conditional Formatting using VBA
Replies
1
Views
351
Joe4
Joe4
C
  • Question Question
One-off conditional formatting duplicate exeptions
Replies
1
Views
485
Artik
Artik
K
  • Solved
vlookup max date
Replies
6
Views
372
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top