How to transpose data from column to rows multiple times - macro?

[Pikkewnytjie]

So, I am fairly competent with excel formula but no nothing about macros. I have just received 35 separate pdfs which I think will convert to around 15000 rows of data in excel. I think it will take me hours (if not days) to formulate the data in to a usable table without some kind of excel magic!

Having pasted from the pdfs, the data has all gone in to column A with some cells including a single piece of data and others including several pieces of data. An example is below.

Each set of data that needs to be transposed to a single row, runs from rows 1 to 5 and then 6 to 10 and this is consistent throughout all the data in column A up to row 15000 (that is approximate number of rows I've not finished pasting it all in yet!).You will see each row has an individual piece of data except rows 3 and 8, which need to be split out in to 5 separate columns (text, date, date, number, number)


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Smith, John
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]123456789
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Couple 01/10/2010 30/09/2011 1,600.00 68.00
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1,668.00
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Page, Michael
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]987654321
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Family 01/01/2011 30/09/2011 2,400.25 75.50
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]2,475.75
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Active
[/TD]
[/TR]
</tbody>[/TABLE]

There will be a row of column headings at the top spanning column A to I to accommodate each piece of data. See how I would like it to look:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Member Number[/TD]
[TD]Cover[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Net Cost[/TD]
[TD]Tax[/TD]
[TD]Total Cost[/TD]
[TD]Current Status[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD]123456789[/TD]
[TD]Couple[/TD]
[TD]01/10/2010[/TD]
[TD]30/09/2011[/TD]
[TD]1,600.00[/TD]
[TD]68.00[/TD]
[TD]1,688.00[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Page, Michael[/TD]
[TD]987654321[/TD]
[TD]Family[/TD]
[TD]01/01/2011[/TD]
[TD]30/09/2011[/TD]
[TD]2,400.25[/TD]
[TD]75.50[/TD]
[TD]2,475.75[/TD]
[TD]Active[/TD]
[/TR]
</tbody>[/TABLE]

I would like to run a macro for rows 1 to 5 to put all the data in the table above in to the table under a new tab. I have no idea how to make this and then repeat it down the whole column, to first, move text to columns on rows 3 and 8 (and so on) and then transpose/cut and paste the rest of the data in to the right columns.

Because what I am dealing with is personal data I can't provide you with the actual spreadsheet I am working on.

I really hope someone can help me.

 

[mhillmann]

Try this out. It will only work if the data is formatted as you say.

The biggest assumption being: The data in each [3] Cell is separated by a space, as in the example. And there are no other spaces in the cell.

This will create a new sheet and put the data into it with no header column. (I figured you could handle that )

Sub macro()


Dim ws As Worksheet
Dim wsRows As Integer
Dim i As Integer


Set ws = ActiveWorkbook.Sheets.Add


wsRows = 1


Sheet4.Activate                                                                           ' <--- Change this sheet name to the one you are using


For i = 1 To Sheet4.UsedRange.Rows.Count Step 5                            ' <---- Here too



    ws.Cells(wsRows, 1) = Cells(i, 1)
    ws.Cells(wsRows, 2) = Cells(i + 1, 1)
    ws.Cells(wsRows, 3) = Split(Cells(i + 2, 1), " ")(0)
    ws.Cells(wsRows, 4) = Split(Cells(i + 2, 1), " ")(1)
    ws.Cells(wsRows, 5) = Split(Cells(i + 2, 1), " ")(2)
    ws.Cells(wsRows, 6) = Split(Cells(i + 2, 1), " ")(3)
    ws.Cells(wsRows, 7) = Split(Cells(i + 2, 1), " ")(4)
    ws.Cells(wsRows, 8) = Cells(i + 3, 1)
    ws.Cells(wsRows, 9) = Cells(i + 4, 1)
    
    wsRows = wsRows + 1
    
Next


ws.Activate


End Sub