Excel transpose how to do.

Ro1966

New Member
Joined
Jan 30, 2019
Messages
2
how can I transpose the info in EXCEL from....to

FROM excel in tab 1

codeproductJanfeb MarAprilMay JuneJulyAugustSeptOctNovDec
310100543494714584753911504235313452702485104734449265515045133348554
310100547123185114161134241125555132307131231120791117887122671128248127822120901
310125823742896884780956757187979079141728457109473979773427708572912
310125827111516103347121524113661119773118799109348106720111051116099115713109449
310142163242432246726418247092603825826237712320024141252392515523793
310142167576075338762777587156187361369564875512957367599745977556539

<colgroup><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


To tab 2

code product
Jan31010054349471
feb 31010054345847
Mar31010054353911
April31010054350423
May 31010054353134
June31010054352702
July31010054348510
August31010054347344
Sept31010054349265
Oct31010054351504
Nov31010054351333
Dec31010054348554
Jan310100547123185
feb 310100547114161
Mar310100547134241
April310100547125555
May 310100547132307
June310100547131231
July310100547120791
August310100547117887
Sept310100547122671
Oct310100547128248
Nov310100547127822
Dec310100547120901
Jan31012582374289
feb 31012582368847
Mar31012582380956
April31012582375718
May 31012582379790
June31012582379141
July31012582372845
August31012582371094

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>
etc
etc
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try:
Code:
Sub TransposeRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, desWS As Worksheet
    Set desWS = Sheets("Sheet2")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    For x = 2 To LastRow
        Range("C1:N1").Copy
        desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
        desWS.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Resize(12, 1) = Range("A" & x)
        desWS.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(12, 1) = Range("B" & x)
        Range("C" & x).Resize(1, 12).Copy
        desWS.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
    Next x
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
another way with PowerQuery

codeproductJanfebMarAprilMayJuneJulyAugustSeptOctNovDec
31010054​
3​
49471​
45847​
53911​
50423​
53134​
52702​
48510​
47344​
49265​
51504​
51333​
48554​
31010054​
7​
123185​
114161​
134241​
125555​
132307​
131231​
120791​
117887​
122671​
128248​
127822​
120901​
31012582​
3​
74289​
68847​
80956​
75718​
79790​
79141​
72845​
71094​
73979​
77342​
77085​
72912​
31012582​
7​
111516​
103347​
121524​
113661​
119773​
118799​
109348​
106720​
111051​
116099​
115713​
109449​
31014216​
3​
24243​
22467​
26418​
24709​
26038​
25826​
23771​
23200​
24141​
25239​
25155​
23793​
31014216​
7​
57607​
53387​
62777​
58715​
61873​
61369​
56487​
55129​
57367​
59974​
59775​
56539​
AttributeValueproductcode
Jan
49471​
3​
31010054​
feb
45847​
3​
31010054​
Mar
53911​
3​
31010054​
April
50423​
3​
31010054​
May
53134​
3​
31010054​
June
52702​
3​
31010054​
July
48510​
3​
31010054​
August
47344​
3​
31010054​
Sept
49265​
3​
31010054​
Oct
51504​
3​
31010054​
Nov
51333​
3​
31010054​
Dec
48554​
3​
31010054​
Jan
123185​
7​
31010054​
feb
114161​
7​
31010054​
Mar
134241​
7​
31010054​
April
125555​
7​
31010054​
May
132307​
7​
31010054​
June
131231​
7​
31010054​
July
120791​
7​
31010054​
August
117887​
7​
31010054​
Sept
122671​
7​
31010054​
Oct
128248​
7​
31010054​
Nov
127822​
7​
31010054​
Dec
120901​
7​
31010054​
Jan
74289​
3​
31012582​
feb
68847​
3​
31012582​
Mar
80956​
3​
31012582​
April
75718​
3​
31012582​
May
79790​
3​
31012582​
June
79141​
3​
31012582​
July
72845​
3​
31012582​
August
71094​
3​
31012582​
Sept
73979​
3​
31012582​
Oct
77342​
3​
31012582​
Nov
77085​
3​
31012582​
Dec
72912​
3​
31012582​
Jan
111516​
7​
31012582​
feb
103347​
7​
31012582​
Mar
121524​
7​
31012582​
April
113661​
7​
31012582​
May
119773​
7​
31012582​
June
118799​
7​
31012582​
July
109348​
7​
31012582​
August
106720​
7​
31012582​
Sept
111051​
7​
31012582​
Oct
116099​
7​
31012582​
Nov
115713​
7​
31012582​
Dec
109449​
7​
31012582​
Jan
24243​
3​
31014216​
feb
22467​
3​
31014216​
Mar
26418​
3​
31014216​
April
24709​
3​
31014216​
May
26038​
3​
31014216​
June
25826​
3​
31014216​
July
23771​
3​
31014216​
August
23200​
3​
31014216​
Sept
24141​
3​
31014216​
Oct
25239​
3​
31014216​
Nov
25155​
3​
31014216​
Dec
23793​
3​
31014216​
Jan
57607​
7​
31014216​
feb
53387​
7​
31014216​
Mar
62777​
7​
31014216​
April
58715​
7​
31014216​
May
61873​
7​
31014216​
June
61369​
7​
31014216​
July
56487​
7​
31014216​
August
55129​
7​
31014216​
Sept
57367​
7​
31014216​
Oct
59974​
7​
31014216​
Nov
59775​
7​
31014216​
Dec
56539​
7​
31014216​

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"code", "product"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Attribute", "Value", "product", "code"})
in
    #"Reordered Columns"[/SIZE]
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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