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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,545
Messages
5,838,008
Members
430,526
Latest member
NiceGuyWithExcel2007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top