Rearrange data.

[Doug24]

I receive data that has a varying number of lines for each "data set."
The first line of each data set begins with the same word - begin.
Then unique lines of data follow, anywhere from 3 to 8 lines.
Then a new data set starts with the word - begin.
The number of data sets varies from day to day - min of 1 and max of 25.

Example using numbers rather than the actual data:

begin
1
2
3
begin
1
2
3
4
5
6
begin
1
2
3
4
5

Is there any way to rearrange the data so that each unique set has 10 lines. So that the first "begin"
would be A1 and the data for that group would fill in under, the second "begin" would be A11 and the
data for that group would fill in under. And so on for the remaining data.

Thanks for any help,
Doug

 

[MickG]

Try:-

Sub nTens()
Dim Rng As Range, Dn As Range, Ray As Variant, c As Long, p As Long, q As Long
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
p = 0
Columns("A:A").ClearContents
Do While c < UBound(Ray, 1)
    c = c + 1
    If Ray(c, 1) = "begin" And c > 1 Then
        p = q
        p = p + 10
        q = p
    End If
    p = p + 1
    Cells(p, 1) = Ray(c, 1)
Loop
End Sub

 

[Doug24]

Thank you MickG - that worked perfectly!
Doug

 

[Doug24]

MickG If you're still out there. Is it possible to extend the data that will be moved
from just column A out to column G? I tried this on a different set of data and need to move
from column A through column G.

This worked so well on the first workbook I thought I would use it in another one.

Thanks for any help,
Doug

 

[MickG]

Do you want the columns A-G to be based on the "begins" in column "A" or based on the "begins" in each separate column

 

[Doug24]

Thanks for your response MickG. I'm new to this forum and wasn't sure if you would see my second question
so I posted a new question to see if someone could help me out and they did. I just changed the code you
gave me and it works great.

Thanks again for your help!
Doug

 

[MickG]

You're welcome