# Rearrange data.

#### Doug24

##### New Member
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

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### MickG

##### MrExcel MVP
Try:-
VBA Code:
``````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

##### New Member
Thank you MickG - that worked perfectly!
Doug

#### Doug24

##### New Member
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

##### MrExcel MVP

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

##### New Member
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.

Doug

You're welcome

Replies
0
Views
66
Replies
5
Views
137
Replies
1
Views
125
Replies
2
Views
53
Replies
3
Views
177

1,126,940
Messages
5,621,727
Members
415,853
Latest member
Newlife72

### 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.

### Which adblocker are you using?

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

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