Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 248
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to change data from horizontal to be vertical (Excel 2007) Suwat Upathambhakul (Thailand) Excel Discussion (Misc queries) 7 April 14th 09 06:58 AM
How do I change horizontal cells to vertical JMP Excel Discussion (Misc queries) 1 August 7th 08 09:40 PM
change table from vertical to horizontal widman Excel Discussion (Misc queries) 0 December 5th 06 03:21 PM
How to make Horizontal data go Vertical tx12345 Excel Worksheet Functions 10 December 24th 05 04:40 AM
How do I change my typing from horizontal to vertical chickaleeka Excel Worksheet Functions 2 December 12th 05 04:18 AM


All times are GMT +1. The time now is 06:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"