Advanced text to columns for groupings of rows

imdcool1

New Member
Joined
Jan 18, 2017
Messages
2
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!!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Derek73

New Member
Joined
Oct 25, 2016
Messages
38
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):
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,688
Messages
5,597,546
Members
414,154
Latest member
thevaper

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
Top