Rearrange data.

Doug24

New Member
Joined
Jul 13, 2015
Messages
10
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
Joined
Jul 13, 2015
Messages
10
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
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

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
Joined
Jul 13, 2015
Messages
10
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,264
Messages
5,600,599
Members
414,393
Latest member
Vignesh Mechz

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