Transposition lookup

Salamullah

Board Regular
Joined
Mar 28, 2011
Messages
221
Please help me to bring data from Horizontal array to vertical area.


Horizontal
VolumeProfit
ProductJanFebMarJanFebMar
A1040701205201680
B205080180250720
C306090150180600
Vertical
ProductMonthVolumeProfit
AJan10120
BJan20180
CJan30150
AFeb40520
BFeb50250
CFeb60180
AMar701680
BMar80720
CMar90600
<colgroup><col width="64" style="width: 48pt;" span="8"> <tbody> </tbody>
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
VBA solution

Code:
Sub k1()
Lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
k = 2
For i = 2 To 4
For j = 4 To Lastrow
k = k + 1
Worksheets("Sheet2").Cells(k, 1) = Worksheets("Sheet1").Cells(j, 1)
Worksheets("Sheet2").Cells(k, 2) = Worksheets("Sheet1").Cells(3, i)
Worksheets("Sheet2").Cells(k, 3) = Worksheets("Sheet1").Cells(j, i)
Worksheets("Sheet2").Cells(k, 4) = Worksheets("Sheet1").Cells(j, i + 4)
Next j
Next i

End Sub

Input on Sheet1 with Horizontal in A1
Output on Sheet2
 

Salamullah

Board Regular
Joined
Mar 28, 2011
Messages
221
Hi Special,

Many thanks and sorry for the late reply I have multi tabs so if excel formula is availble than it will be great !
 

Watch MrExcel Video

Forum statistics

Threads
1,108,922
Messages
5,525,645
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top