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)

	Column A
1	Smith, John
2	123456789
3	Couple 01/10/2010 30/09/2011 1,600.00 68.00
4	1,668.00
5	Active
6	Page, Michael
7	987654321
8	Family 01/01/2011 30/09/2011 2,400.25 75.50
9	2,475.75
10	Active

<tbody>
</tbody>

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:

Name	Member Number	Cover	From	To	Net Cost	Tax	Total Cost	Current Status
Smith, John	123456789	Couple	01/10/2010	30/09/2011	1,600.00	68.00	1,688.00	Active
Page, Michael	987654321	Family	01/01/2011	30/09/2011	2,400.25	75.50	2,475.75	Active

<tbody>
</tbody>

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