Advanced text to columns for groupings of rows

[imdcool1]

I have data that is organized where a group of data is every 5 rows, is there any way to break it out into columns? So for example my data is:

Row 1: First Name | Last Name | Gender
Row 2: Unique ID | (blank) | (blank)
Row 3: (blank) | (blank) | (blank)
Row 4: Email | (blank) | (blank)
Row 5:Phone Number| (blank) | (blank)
Row 6:First Name | Last Name | Gender
Row 7: Unique ID | (blank) | (blank)
Row 8: (blank) | (blank) | (blank)
Row 9: Email | (blank) | (blank)
Row 10:Phone Number| (blank) | (blank)

It continues like that for a ton of rows, so every 5 rows has all of the data for 1 person (and part of their data is in other columns in the first row).

I'd like to change it to be columns that are:
First Name | Last Name | Gender | Unique ID | Email |Phone Number
Note that I'm OK with having blank columns as well.

Is there any formula or build in function to do this? I know the data is arranged terribly, but unfortunately I am stuck with it arranged that way

Thanks so much!!

 

[Derek73]

Can't think of a formula or built in function that will do this. However, since it's fairly straightforward format, we can parse it with VBA. Assuming there's no header rows and the data starts in row 1 (other wise adjust the for loop to start at that row instead of 1):

Sub Test_imdcool1()


Dim src As Worksheet
Dim tgt As Worksheet
Dim tgtRow As Long
Dim l As Integer
Dim lr As Integer
Dim ExcelLastCell As Variant
Dim Row As Long
Set src = ActiveSheet
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
Row = ExcelLastCell.Row
Do While Application.CountA(ActiveSheet.Rows(Row)) = 0 And Row <> 1
    Row = Row - 1
Loop
lr = Row
Set tgt = Worksheets.Add
tgtRow = 1
For l = 1 To lr Step 5
    tgt.Cells(tgtRow, 1).Value = src.Cells(l, 1)
    tgt.Cells(tgtRow, 2).Value = src.Cells(l, 2)
    tgt.Cells(tgtRow, 3).Value = src.Cells(l, 3)
    tgt.Cells(tgtRow, 4).Value = src.Cells(l + 1, 1)
    tgt.Cells(tgtRow, 5).Value = src.Cells(l + 3, 1)
    tgt.Cells(tgtRow, 6).Value = src.Cells(l + 4, 1)
    tgtRow = tgtRow + 1
Next l
End Sub

 

[imdcool1]

Thank you so much Derek73!!! That worked perfectly!!!