Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to change data from horizontal to be vertical (Excel 2003)
I got Bill of materials in showing Raw Materials usage in horizontal
like this RM1 RM2 RM3 RM4 RM5 FG1 5 6 3 2 FG2 2 7 1 FG3 7 5 2 but I would like to change it to be vertical as follow FG1 RM1 5 RM2 6 RM4 3 RM5 2 FG2 RM1 2 RM3 7 RM5 1 FG3 RM2 7 RM3 5 RM4 2 could you please advise how should we do best rgds, suwat u. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to change data from horizontal to be vertical (Excel 2003)
Sub Transpose()
Dim lastRow, lastCol As Long Dim srcSheet As String Dim destSheet As String Dim i, j, k As Long Dim RM() As String Application.ScreenUpdating = False srcSheet = "Sheet1" destSheet = "Sheet2" Worksheets(srcSheet).Activate With Worksheets(srcSheet) lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With ReDim RM(lastCol) For j = 2 To lastCol RM(j) = Worksheets(srcSheet).Cells(1, j) Next k = 2 For i = 2 To lastRow Sheets(destSheet).Cells(k, 1) = Worksheets(srcSheet).Cells(i, 1) Sheets(destSheet).Cells(k, 2) = Worksheets(srcSheet).Cells(i, 2) For j = 2 To lastCol If (Worksheets(srcSheet).Cells(i, j) < "") Then Sheets(destSheet).Cells(k, 3) = Worksheets(srcSheet).Cells(i, j) k = k + 1 End If Next j Next i Worksheets(destSheet).Activate Application.ScreenUpdating = True End Sub "Suwat Upathambhakul (Thailand)" wrote: I got Bill of materials in showing Raw Materials usage in horizontal like this RM1 RM2 RM3 RM4 RM5 FG1 5 6 3 2 FG2 2 7 1 FG3 7 5 2 but I would like to change it to be vertical as follow FG1 RM1 5 RM2 6 RM4 3 RM5 2 FG2 RM1 2 RM3 7 RM5 1 FG3 RM2 7 RM3 5 RM4 2 could you please advise how should we do best rgds, suwat u. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to change data from horizontal to be vertical (Excel 2003)
Step 1-Copy entire horizontal data Step2- Click in a blank cell step 2-select paste special step 3-select Transpose press OK You will get all the data vertically. Further it becomes easy to get the data as required. "Suwat Upathambhakul (Thailand)" wrote: I got Bill of materials in showing Raw Materials usage in horizontal like this RM1 RM2 RM3 RM4 RM5 FG1 5 6 3 2 FG2 2 7 1 FG3 7 5 2 but I would like to change it to be vertical as follow FG1 RM1 5 RM2 6 RM4 3 RM5 2 FG2 RM1 2 RM3 7 RM5 1 FG3 RM2 7 RM3 5 RM4 2 could you please advise how should we do best rgds, suwat u. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to change data from horizontal to be vertical (Excel 2003)
Just copy your data
Then click paste Special And Tick the Transpose box Regards Derek "Sheeloo" just remove all As... wrote in message ... Sub Transpose() Dim lastRow, lastCol As Long Dim srcSheet As String Dim destSheet As String Dim i, j, k As Long Dim RM() As String Application.ScreenUpdating = False srcSheet = "Sheet1" destSheet = "Sheet2" Worksheets(srcSheet).Activate With Worksheets(srcSheet) lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With ReDim RM(lastCol) For j = 2 To lastCol RM(j) = Worksheets(srcSheet).Cells(1, j) Next k = 2 For i = 2 To lastRow Sheets(destSheet).Cells(k, 1) = Worksheets(srcSheet).Cells(i, 1) Sheets(destSheet).Cells(k, 2) = Worksheets(srcSheet).Cells(i, 2) For j = 2 To lastCol If (Worksheets(srcSheet).Cells(i, j) < "") Then Sheets(destSheet).Cells(k, 3) = Worksheets(srcSheet).Cells(i, j) k = k + 1 End If Next j Next i Worksheets(destSheet).Activate Application.ScreenUpdating = True End Sub "Suwat Upathambhakul (Thailand)" wrote: I got Bill of materials in showing Raw Materials usage in horizontal like this RM1 RM2 RM3 RM4 RM5 FG1 5 6 3 2 FG2 2 7 1 FG3 7 5 2 but I would like to change it to be vertical as follow FG1 RM1 5 RM2 6 RM4 3 RM5 2 FG2 RM1 2 RM3 7 RM5 1 FG3 RM2 7 RM3 5 RM4 2 could you please advise how should we do best rgds, suwat u. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change data from horizontal to be vertical (Excel 2007) | Excel Discussion (Misc queries) | |||
How do I change horizontal cells to vertical | Excel Discussion (Misc queries) | |||
change table from vertical to horizontal | Excel Discussion (Misc queries) | |||
How to make Horizontal data go Vertical | Excel Worksheet Functions | |||
How do I change my typing from horizontal to vertical | Excel Worksheet Functions |