Need urgent help!!how can i transpose huge data

[HUZZAIN]

HELLO
I want to transpose selected rows into coloumns and their values under them in different sheet.I want a vbscript or macro to perform the task as data is big.
eg;

TARIH	KOD	DOSYA	GELIŞ	TOPIC	RESULTS
26.06.2014 08:07	901020	800478	60	Demir (Serum)	16
26.06.2014 08:07	901040	800478	60	Demir bağlama kapasitesi	362
26.06.2014 08:07	901500	800478	60	Glukoz	109
26.06.2014 08:07	901450	800478	60	HBA1C	5,6

<colgroup><col><col span="3"><col><col></colgroup><tbody>
</tbody>

04.05.2014 16:50	901020	801199	51	Demir (Serum)	41
04.05.2014 16:50	901040	801199	51	Demir bağlama kapasitesi	215
04.05.2014 16:50	901220	801199	51	Ferritin	56,46

<colgroup><col><col span="3"><col><col></colgroup><tbody>
</tbody>

above are the few rows of the data as you can see there are columns in the sheet kod,dosya,tarıh etc.Dosya is basically the file number and its changing after all the time and its values respectively.I want to make the Column (topic) into differnt columns and move their values under them.How can i do it without doing it manually.
thanks

 

[Fishboy]

HELLO
I want to transpose selected rows into coloumns and their values under them in different sheet.I want a vbscript or macro to perform the task as data is big.
eg;

TARIH	KOD	DOSYA	GELIŞ	TOPIC	RESULTS
26.06.2014 08:07	901020	800478	60	Demir (Serum)	16
26.06.2014 08:07	901040	800478	60	Demir bağlama kapasitesi	362
26.06.2014 08:07	901500	800478	60	Glukoz	109
26.06.2014 08:07	901450	800478	60	HBA1C	5,6

<tbody>
</tbody>

04.05.2014 16:50	901020	801199	51	Demir (Serum)	41
04.05.2014 16:50	901040	801199	51	Demir bağlama kapasitesi	215
04.05.2014 16:50	901220	801199	51	Ferritin	56,46

<tbody>
</tbody>

above are the few rows of the data as you can see there are columns in the sheet kod,dosya,tarıh etc.Dosya is basically the file number and its changing after all the time and its values respectively.I want to make the Column (topic) into differnt columns and move their values under them.How can i do it without doing it manually.
thanks

Hi HUZZAIN, welcome to the boards.

Try this out in a COPY of your workbook. The following code assumes your source data is on a sheet named Sheet1 and you are moving / transposing over to a sheet named Sheet2. You can obviously change that as required by amending the bold red parts in my script. This macro is added to a standard module and can be applied to a button to be run or by pressing Alt+F8 and selecting it from there. The code will work its way down as far as there are topics in column E.

Sub TransposeMassData()
Dim Cell As Range, cRange As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim DestCol As Integer
Application.ScreenUpdating = False
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    DestCol = 2
        ws1.Range("E1").Copy Destination:=ws2.Range("A1")
            ws1.Range("A1").Copy Destination:=ws2.Range("A2")
                ws1.Range("B1").Copy Destination:=ws2.Range("A3")
                    ws1.Range("C1").Copy Destination:=ws2.Range("A4")
                        ws1.Range("D1").Copy Destination:=ws2.Range("A5")
                            ws1.Range("F1").Copy Destination:=ws2.Range("A6")
            LastRow = ws1.Cells(Rows.Count, "E").End(xlUp).Row
                Set cRange = ws1.Range("E2:E" & LastRow)
                    For Each Cell In cRange
                        Cell.Copy Destination:=ws2.Cells(1, DestCol)
                            Cell.Offset(0, -4).Copy Destination:=ws2.Cells(2, DestCol)
                                Cell.Offset(0, -3).Copy Destination:=ws2.Cells(3, DestCol)
                                    Cell.Offset(0, -2).Copy Destination:=ws2.Cells(4, DestCol)
                                        Cell.Offset(0, -1).Copy Destination:=ws2.Cells(5, DestCol)
                                            Cell.Offset(0, 1).Copy Destination:=ws2.Cells(6, DestCol)
                            DestCol = DestCol + 1
                    Next Cell
Application.ScreenUpdating = True
End Sub