Solved

Split data over rows in excel

Posted on 2011-09-09
8
283 Views
Last Modified: 2012-05-12
Guys I think this should be straight forward but I need some assistance.
I have a list of information that spans from column A2 to column H500 but it would vary. This information needs to be copied into another sheet but needs to have 8 rows separating each of the rows of data this is so that the list fits into a formatted sheet.

I have attached an example

Can anyone help

Regards

Darren
EE-Sample.xls
0
Comment
Question by:DarrenJackson
  • 5
  • 3
8 Comments
 
LVL 19

Expert Comment

by:akoster
ID: 36509221
So in effect you want to transform

row1
row2
row3
[...]

to

row1
empty_row_1
empty_row_2
empty_row_3
empty_row_4
empty_row_5
empty_row_6
empty_row_7
empty_row_8
row2
empty_row_1
empty_row_2
empty_row_3
empty_row_4
empty_row_5
empty_row_6
empty_row_7
empty_row_8
row3
empty_row_1
empty_row_2
empty_row_3
empty_row_4
empty_row_5
empty_row_6
empty_row_7
empty_row_8
[...]
0
 
LVL 19

Expert Comment

by:akoster
ID: 36509260
this can be done by running a macro such as

Sub perform_copy()

    target_sheet = "Sheet2"
    target_row = 1
    For Each Row In UsedRange.Rows
        Row.copy Worksheets(target_sheet).Cells(target_row, 1)
        target_row = target_row + 8
    Next Row

End Sub

Open in new window

0
 

Author Comment

by:DarrenJackson
ID: 36510052
akoster

thanks for replying but the code when pasted into the sample sheet I attached doesnt seem to work.

I assume it need to be pasted into the VB Sheet1 object but it errors after copying 1 line

Regards
0
 
LVL 19

Expert Comment

by:akoster
ID: 36510632
DarrenJackson,

that would be because of :

 - the sheet "Sheet2" does not exist
 - there should be an offset : firstname is located in column A in list and column B in new sheet.

I'm sorry i did not use your sheet, this would be the better solution:
Sub perform_copy()

    target_sheet = "New Sheet"
    target_row = 28
    target_column = 2
    For Each Row In UsedRange.Rows
        If Row.Row > 1 Then
            '-- copy data rows but skip first row (header)
            Row.Copy Worksheets(target_sheet).Cells(target_row, target_column)
            target_row = target_row + 8
        End If
    Next Row

End Sub

Open in new window


EE-Sample.xls
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:DarrenJackson
ID: 36510789
Akoster thanks again. Im seeing ti working but my formatting is looking to be causing issues with the way the info is copied over I am unable to copy the data to rows 4..12 etc  in the new sheet.

Any Ideas

Cheers
0
 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
ID: 36510840
I guess this is caused by the use of combined cells. This will work :
Sub perform_copy()

    target_sheet = "New Sheet"
    target_row = 4
    target_column_offset = 1
    For Each Row In UsedRange.Rows
        If Row.Row > 1 Then
            '-- copy data rows but skip first row (header)
            For col = 1 To 8
                Worksheets(target_sheet).Cells(target_row, target_column_offset + col) = Row.Cells(col)
            Next col
            target_row = target_row + 8
        End If
    Next Row

End Sub

Open in new window

0
 

Author Comment

by:DarrenJackson
ID: 36510919
Akoster perfect

This is excatly what I need thanks

Regards

Darren
0
 
LVL 19

Expert Comment

by:akoster
ID: 36511244
you're welcome !
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now