How to bring horizantal data into vertical

S

Salamullah

Board Regular
Joined
Mar 28, 2011
Messages
221
Hi,

I have month wise data Horizontally which I want to bring vertically with formula, please help.

Horizontal Vertical
[TABLE="width: 1292"]
<tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 3"]Volume[/TD]
[TD] [/TD]
[TD="colspan: 3"]Sales[/TD]
[TD] [/TD]
[TD="colspan: 3"]Cost[/TD]
[TD] [/TD]
[TD="colspan: 3"]Profit[/TD]
[TD] [/TD]
[TD]Product[/TD]
[TD]Area[/TD]
[TD]Month[/TD]
[TD]Volume[/TD]
[TD]Sales[/TD]
[TD]Cost[/TD]
[TD]Profit[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Area[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD] [/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD] [/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD] [/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]BLACK[/TD]
[TD] 200 [/TD]
[TD] 300 [/TD]
[TD] 151 [/TD]
[TD] [/TD]
[TD] 19,000 [/TD]
[TD] 28,500 [/TD]
[TD] 14,345 [/TD]
[TD] [/TD]
[TD] 2,500 [/TD]
[TD] 7,500 [/TD]
[TD] 3,775 [/TD]
[TD] [/TD]
[TD] 16,500 [/TD]
[TD] 21,000 [/TD]
[TD] 10,570 [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]BLACK[/TD]
[TD]Jan[/TD]
[TD="align: right"]200[/TD]
[TD] 19,000 [/TD]
[TD] 2,500 [/TD]
[TD] 16,500 [/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]BLACK[/TD]
[TD] 220 [/TD]
[TD] 315 [/TD]
[TD] 171 [/TD]
[TD] [/TD]
[TD] 20,900 [/TD]
[TD] 29,925 [/TD]
[TD] 16,245 [/TD]
[TD] [/TD]
[TD] 3,000 [/TD]
[TD] 7,875 [/TD]
[TD] 4,275 [/TD]
[TD] [/TD]
[TD] 17,900 [/TD]
[TD] 22,050 [/TD]
[TD] 11,970 [/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]BLACK[/TD]
[TD]Jan[/TD]
[TD="align: right"]220[/TD]
[TD] 20,900 [/TD]
[TD] 3,000 [/TD]
[TD] 17,900 [/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]RED[/TD]
[TD] 240 [/TD]
[TD] 330 [/TD]
[TD] 191 [/TD]
[TD] [/TD]
[TD] 22,800 [/TD]
[TD] 31,350 [/TD]
[TD] 18,145 [/TD]
[TD] [/TD]
[TD] 3,500 [/TD]
[TD] 8,250 [/TD]
[TD] 4,775 [/TD]
[TD] [/TD]
[TD] 19,300 [/TD]
[TD] 23,100 [/TD]
[TD] 13,370 [/TD]
[TD] [/TD]
[TD]C[/TD]
[TD]RED[/TD]
[TD]Jan[/TD]
[TD="align: right"]240[/TD]
[TD] 22,800 [/TD]
[TD] 3,500 [/TD]
[TD] 19,300 [/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]GREEN[/TD]
[TD] 260 [/TD]
[TD] 345 [/TD]
[TD] 211 [/TD]
[TD] [/TD]
[TD] 24,700 [/TD]
[TD] 32,775 [/TD]
[TD] 20,045 [/TD]
[TD] [/TD]
[TD] 4,000 [/TD]
[TD] 8,625 [/TD]
[TD] 5,275 [/TD]
[TD] [/TD]
[TD] 20,700 [/TD]
[TD] 24,150 [/TD]
[TD] 14,770 [/TD]
[TD] [/TD]
[TD]E[/TD]
[TD]GREEN[/TD]
[TD]Jan[/TD]
[TD="align: right"]260[/TD]
[TD] 24,700 [/TD]
[TD] 4,000 [/TD]
[TD] 20,700 [/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]GREEN[/TD]
[TD] 280 [/TD]
[TD] 360 [/TD]
[TD] 231 [/TD]
[TD] [/TD]
[TD] 26,600 [/TD]
[TD] 34,200 [/TD]
[TD] 21,945 [/TD]
[TD] [/TD]
[TD] 4,500 [/TD]
[TD] 9,000 [/TD]
[TD] 5,775 [/TD]
[TD] [/TD]
[TD] 22,100 [/TD]
[TD] 25,200 [/TD]
[TD] 16,170 [/TD]
[TD] [/TD]
[TD]D[/TD]
[TD]GREEN[/TD]
[TD]Jan[/TD]
[TD="align: right"]280[/TD]
[TD] 26,600 [/TD]
[TD] 4,500 [/TD]
[TD] 22,100 [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]BLUE[/TD]
[TD] 300 [/TD]
[TD] 375 [/TD]
[TD] 251 [/TD]
[TD] [/TD]
[TD] 28,500 [/TD]
[TD] 35,625 [/TD]
[TD] 23,845 [/TD]
[TD] [/TD]
[TD] 5,000 [/TD]
[TD] 9,375 [/TD]
[TD] 6,275 [/TD]
[TD] [/TD]
[TD] 23,500 [/TD]
[TD] 26,250 [/TD]
[TD] 17,570 [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]BLUE[/TD]
[TD]Jan[/TD]
[TD="align: right"]300[/TD]
[TD] 28,500 [/TD]
[TD] 5,000 [/TD]
[TD] 23,500 [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]GREEN[/TD]
[TD] 320 [/TD]
[TD] 390 [/TD]
[TD] 271 [/TD]
[TD] [/TD]
[TD] 30,400 [/TD]
[TD] 37,050 [/TD]
[TD] 25,745 [/TD]
[TD] [/TD]
[TD] 5,500 [/TD]
[TD] 9,750 [/TD]
[TD] 6,775 [/TD]
[TD] [/TD]
[TD] 24,900 [/TD]
[TD] 27,300 [/TD]
[TD] 18,970 [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]GREEN[/TD]
[TD]Jan[/TD]
[TD="align: right"]320[/TD]
[TD] 30,400 [/TD]
[TD] 5,500 [/TD]
[TD] 24,900 [/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD] [/TD]
[TD] 1,820 [/TD]
[TD] 2,415 [/TD]
[TD] 1,477 [/TD]
[TD] [/TD]
[TD] 172,900 [/TD]
[TD] 229,425 [/TD]
[TD] 140,315 [/TD]
[TD] [/TD]
[TD] 28,000 [/TD]
[TD] 60,375 [/TD]
[TD] 36,925 [/TD]
[TD] [/TD]
[TD] 144,900 [/TD]
[TD] 169,050 [/TD]
[TD] 103,390 [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]BLACK[/TD]
[TD]Feb[/TD]
[TD="align: right"]300[/TD]
[TD] 28,500 [/TD]
[TD] 7,500 [/TD]
[TD] 21,000 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 417,340 [/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]BLACK[/TD]
[TD]Feb[/TD]
[TD="align: right"]315[/TD]
[TD] 29,925 [/TD]
[TD] 7,875 [/TD]
[TD] 22,050 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]C[/TD]
[TD]RED[/TD]
[TD]Feb[/TD]
[TD="align: right"]330[/TD]
[TD] 31,350 [/TD]
[TD] 8,250 [/TD]
[TD] 23,100 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]E[/TD]
[TD]GREEN[/TD]
[TD]Feb[/TD]
[TD="align: right"]345[/TD]
[TD] 32,775 [/TD]
[TD] 8,625 [/TD]
[TD] 24,150 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]D[/TD]
[TD]GREEN[/TD]
[TD]Feb[/TD]
[TD="align: right"]360[/TD]
[TD] 34,200 [/TD]
[TD] 9,000 [/TD]
[TD] 25,200 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]BLUE[/TD]
[TD]Feb[/TD]
[TD="align: right"]375[/TD]
[TD] 35,625 [/TD]
[TD] 9,375 [/TD]
[TD] 26,250 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]GREEN[/TD]
[TD]Feb[/TD]
[TD="align: right"]390[/TD]
[TD] 37,050 [/TD]
[TD] 9,750 [/TD]
[TD] 27,300 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]BLACK[/TD]
[TD]Mar[/TD]
[TD="align: right"]151[/TD]
[TD] 14,345 [/TD]
[TD] 3,775 [/TD]
[TD] 10,570 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]BLACK[/TD]
[TD]Mar[/TD]
[TD="align: right"]171[/TD]
[TD] 16,245 [/TD]
[TD] 4,275 [/TD]
[TD] 11,970 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]C[/TD]
[TD]RED[/TD]
[TD]Mar[/TD]
[TD="align: right"]191[/TD]
[TD] 18,145 [/TD]
[TD] 4,775 [/TD]
[TD] 13,370 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]E[/TD]
[TD]GREEN[/TD]
[TD]Mar[/TD]
[TD="align: right"]211[/TD]
[TD] 20,045 [/TD]
[TD] 5,275 [/TD]
[TD] 14,770 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]D[/TD]
[TD]GREEN[/TD]
[TD]Mar[/TD]
[TD="align: right"]231[/TD]
[TD] 21,945 [/TD]
[TD] 5,775 [/TD]
[TD] 16,170 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]BLUE[/TD]
[TD]Mar[/TD]
[TD="align: right"]251[/TD]
[TD] 23,845 [/TD]
[TD] 6,275 [/TD]
[TD] 17,570 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]GREEN[/TD]
[TD]Mar[/TD]
[TD="align: right"]271[/TD]
[TD] 25,745 [/TD]
[TD] 6,775 [/TD]
[TD] 18,970 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 417,340 [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col span="3"><col><col span="3"><col><col span="3"><col><col span="3"><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
I can suggest VB code to achieve your result. If you need only formula to solve your problem, I have no idea. Probably some other expert may help you with formula.
 
Upvote 0
Try this:
Code: 
Private Sub cmdHorizontalIntoVertical_Click()
 Dim WayNam As String
 Dim FilNam As String
 Dim SheetNam As String
 Dim LastRecNum As Long
 
 WayNam = InputBox("Type the path name of the" & vbCrLf & _
  "Excel Source File:", "Path of your File!", "D\") 'make sure \ is typed
 FilNam = InputBox("Type the name of the" & vbCrLf & _
  "Excel Source File:", "Your File's Name!", "HorizontalDataToVertical.xlsx") 'Type with extension
 SheetNam = InputBox("Type the name of the" & vbCrLf & _
 "the Sheet in the Source File: ", "Your Sheet's Name!", "DestPage")
 Workbooks.Open Filename:=WayNam & FilNam
 Sheets(SheetNam).Select
 LastRecNum = Cells(Rows.Count, "A").End(xlUp).Row 'to go to last data cell inspite of blank cells
 MsgBox "lastrec=" & LastRecNum


 Dim NowRec As Long
 Dim SourceCol As Integer
 Dim SourceRow As Long
 Dim DestinationCol As Integer
 Dim DestinationRow As Long
 Dim NowRow As Long
 Dim CurRec As Long
 Dim BgnCol As Integer
 Dim DataRow As Integer
 NowRec = 4
 SourceCol = 1
 SourceRow = 4
 DestinationCol = 18
 
 Dim MyMonth As String
 Dim MyProductArea(1, 6) As String
 
 Dim i As Integer, j As Integer, k As Integer
 i = 1
 DestinationRow = 4
 Do While DestinationRow < LastRecNum
 
 For i = 1 To 3
  DataRow = 4
  If i = 1 Then
   MyMonth = "Jan"
   BgnCol = 3
   DestinationRow = 3
  ElseIf i = 2 Then
   MyMonth = "Feb"
   BgnCol = 4
  ElseIf i = 3 Then
   MyMonth = "Mar"
   BgnCol = 5
  End If 'i = 1
  If i = 1 Then
   Cells(DestinationRow - 1, 19).Value = "Product"
   Cells(DestinationRow - 1, 20).Value = "Area"
   Cells(DestinationRow - 1, 21).Value = "Month"
   Cells(DestinationRow - 1, 22).Value = "Volume"
   Cells(DestinationRow - 1, 23).Value = "Sales"
   Cells(DestinationRow - 1, 24).Value = "Cost"
   Cells(DestinationRow - 1, 25).Value = "Profit"


   For SourceCol = 1 To 2
    For NowRow = 0 To 6
     SourceRow = NowRow + 4
     MyProductArea(SourceCol - 1, NowRow) = Cells(SourceRow, SourceCol)
     'while source col is 0 myproductarea = A, B, C, E, D, A, A
     'while source col is 1 myproductarea = BLACK, BLACK, RED, GREEN, GREEN, BLUE, GREEN
    Next 'nowRow = 0 To 6
   Next 'SourceCol = 1 To 2
  End If ' i = 1
  For NowRow = 0 To 6
   DestinationRow = DestinationRow + 1
   SourceCol = 0
    For DestinationCol = 19 To 20
     SourceCol = SourceCol + 1
     Cells(DestinationRow, DestinationCol).Value = MyProductArea(SourceCol - 1, NowRow)
    Next 'DestinationCol = 19 to 25
    Cells(DestinationRow, DestinationCol).Value = MyMonth
    For SourceCol = BgnCol To 17 Step 4
     DestinationCol = DestinationCol + 1
     Cells(DestinationRow, DestinationCol).Value = Cells(DataRow, SourceCol).Value
    Next 'SourceCol = 3 To 17 Step 4
    DataRow = DataRow + 1
  Next 'nowRow = 0 To 6
 Next 'i = 1 to 3
 If i = 3 Then
  Exit Do
 End If
 Loop 'CurRec < LastRecNum
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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
Back
Top