Need code for transposing data

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi All,

My orginal data looks like this (SAP generated)
stock.xls
ABCDEFGHIJ
1
2Plant1011abc ltd
3Materialx1010001
4Descriptionitem descr1
5
6Stock on01.04.20052,974PC
7Receipts Total21,250PC
8Issues Total18,693PC
9Stock on05.04.20055,531PC
10
11Plant1011abc ltd
12Materialx1010002
13Descriptionitem descr2
14
15Stock on01.04.200510,085PC
16Receipts Total62,880PC
17Issues Total38,400PC
18Stock on05.04.200534,565PC
19
stock


And I need the result sheet like the following..
stock.xls
LMNOPQ
3MaterialDescriptionOpng stockReceipts TotalIssues TotalClsg Stock
4x1010001item descr12,97421250186935,531
5x1010002item descr210,085628803840034,565
6
stock


Any help would be appreciated!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
Sub Test()
    Dim Sh As Worksheet
    Dim LastRow As Long
    Dim i As Long
    Dim r As Long
    Set Sh = Worksheets("stock")
    LastRow = Sh.Range("B65536").End(xlUp).Row
    r = 3
    With Sh
        .Cells(r, 12).Value = "Material"
        .Cells(r, 13).Value = "Description"
        .Cells(r, 14).Value = "Opng stock"
        .Cells(r, 15).Value = "Receipts Total"
        .Cells(r, 16).Value = "Issues Total"
        .Cells(r, 17).Value = "Clsg Stock"
    End With
    r = r + 1
    For i = 2 To LastRow Step 9
        With Sh
            .Cells(r, 12).Value = .Cells(i + 1, 4).Value
            .Cells(r, 13).Value = .Cells(i + 2, 4).Value
            .Cells(r, 14).Value = .Cells(i + 4, 8).Value
            .Cells(r, 15).Value = .Cells(i + 5, 7).Value
            .Cells(r, 16).Value = .Cells(i + 6, 7).Value
            .Cells(r, 17).Value = .Cells(i + 7, 8).Value
        End With
        r = r + 1
    Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,223
Messages
5,570,975
Members
412,352
Latest member
Radek Z
Top