Report Generating from an Unknown Quantity of Data

[saidintrue]

Hello Everyone,

Aim : I am trying to generate an easily readable report in excel. The problem I am having is that my solution must work for 1 result or 500 and the amount of data is never the same.

Background : Results are generated in text files of approximatly 300 rows of data. Between me and a co-worker we have managed to create an program in excel (2003) that can analyse any number of these text files automatically, remove "negative" results, leaving only the core data.

Results format : Each result has a heading, subheading and then random number of results. i.e.
Bob
Work
1
2
3

Currently the macro's work by placing this data in a new sheet in column A under each other using Enddown, offsetting by 1 and pasting (i then manually transfer the data where i want it). e.g.
Bob
Work
1
2
3
John
work
1

Report : I would like to generate a report so that each result goes in a column, seperated by empty columns BUT only using columns A-K , they can go down the page as much as needs to but not across if at all possible (Im trying to aim this at people that cant use PC's and dont have time to work out whats going on)
The other problem is that Bob and Work sections are constants but the numbers could be 1, 2, 3 or 1,2, ..50 etc and it is possible that there could be 1 result or 500.

Basically..is it possible to take a random number of results, of varying lengths and displaying them in a nice neat report on one worksheet?

To make this easier to understand i've made a mock up of what im after in an attachment. 2 sheets, one with the "raw" data and the second with what im would like this to look like.

My appologies if this is a little hard to understand but my thanks in advance, even if you took the time to read this

[JBeaucaire]

If you edit your macro that creates the column A listing so that there is a blank space between each data set, this gets really easy. Just change your Offset(1,0) to Offset(2,0) in that code and that should do it.

Option Explicit

Sub ReformatData()
'Jerry Beaucaire   (5/12/2010)
Dim CpyRNG  As Range
Dim Sctn    As Long
Dim CpyCol  As Long
Dim NextRow As Long
Dim wsRpt   As Worksheet
Dim wsRaw   As Worksheet

Set wsRaw = Sheets("Raw Results")
Set wsRpt = Sheets("Report")

wsRpt.Cells.Clear
Set CpyRNG = wsRaw.Range("A:A").SpecialCells(xlCellTypeConstants)
CpyCol = 1
NextRow = 1

For Sctn = 1 To CpyRNG.Areas.Count
    CpyRNG.Areas(Sctn).Copy wsRpt.Cells(NextRow, CpyCol)
    If CpyCol + 2 > 11 Then
        CpyCol = 1
        NextRow = wsRpt.Cells.Find("*", wsRpt.Cells(Rows.Count, Columns.Count), _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2
    Else
        CpyCol = CpyCol + 2
    End If
Next Sctn

wsRpt.Columns.AutoFit
End Sub

[saidintrue]

Thankyou very much, i could worship the ground you walk on :D