# Need code for transposing data

#### Krishnakumar

##### Well-known Member
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
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``````

#### Krishnakumar

##### Well-known Member
Hi Andrew,

Thanks! Exactly what I wanted.

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

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.

### Which adblocker are you using?

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

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