Separate data in one row into several rows

[DamianT]

I am trying to work out how or if this is possible!

I have data which appears on one line that I need to separate into single lines. The information is being downloaded from a form where a manger can add multiple members of staff, which when downloaded to excel all appears as continual data on one line. A new row appears for each manager.

Below is a sample of the info for one person, these headers are repeated for as many people on the form. So some rows may have 10 employees other rows may only have 2. (make sense?)

Last Name First Name Middle Initial Email Shipping Address 1 Shipping Address 2 City State (Province) Zipcode Country Primary Phone

Question is can these be separated so they all appear stacked rather than in a row?

[venkat1926]

"appear stacked rather than in a row?" NEEDS CLARIFICATION

HAD You given a sample copy it would have been helpful. anyhow see attached file sheet 1
I expect your data is like this in the upper potion of sheet
the result you need is something like this in the lower portion. sometimes example is better than thousand words.

[DamianT]

Thank you for a quick reply.

Sorry I wasn't clearer, first time posting and now I reread it I did need more clarification.

I am re attaching the file with the current format and what I need it to eventually to be.

Many Thanks

Damian

DAIMIAN.xlsx

[mike7952]

Give this a try

DAIMIAN_v2.xlsm

Edit: Results are added to sheet2

[DamianT]

Thank you Thank you so much this is great, exactly what I needed.

[jindon]

Try

Option Explicit

Sub test()
    Dim a, b, i As Long, ii As Long, n As Long
    a = Sheets("sheet1").Range("a1").CurrentRegion
    ReDim b(1 To UBound(a, 2), 1 To UBound(a, 1))
    For i = 1 To UBound(a, 2)
        b(i, 1) = a(1, i)
    Next
    n = 1
    For i = 2 To UBound(a, 1)
        If a(i, 1) <> "Last Name" Then
            n = n + 1
            b(1, n) = a(i, 1)
            For ii = 2 To UBound(a, 2)
                b(ii, n) = a(i, ii)
            Next
        End If
    Next
    With Sheets("sheet2").Cells(1).Resize(UBound(b, 1), n)
        .CurrentRegion.ClearContents
        .Value = b
        .Parent.Activate
    End With
End Sub

[mike7952]

@Jindon,

I think the workbook the OP uploaded is a little confusing. Look down on row 31. If my code worked then I guessed right about everything above row 31 needs to be deleted an that is what the OP data looks like. So all the data above row 31 is not really there in actual worksheet.

[jindon]

My solution is only based on the file uploaded by OP, nothing else.

[mike7952]

If you look at post #2 where Venkat uploaded the wb and the OP modified his upload below starting in row 31

[jindon]

Ahh, I think I missed that one

Sub test()
    Dim i As Long, ii As Long, n As Long
    With Sheets("sheet1").Range("a1").CurrentRegion
        .Rows(1).Resize(, 12).Copy Sheets("sheet2").Range("a1")
        n = 1
        For i = 2 To .Rows.Count
            For ii = 1 To .Columns.Count Step 12
                n = n + 1
                .Cells(i, ii).Resize(, 12).Copy Sheets("sheet2").Cells(n, 1)
        Next ii, i
    End With
End Sub