Insert fixed number of rows and transpose part of the columns

[excel1404]

Hello Gurus,

Seeking your expert help in writing a Macro. Am a newbie.

I have a below table:

[TABLE="width: 631"]
<colgroup><col><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Sold On[/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]Item[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Grapes[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Tomato[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Potato[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]



Expectation is:
1. to Create 6 lines for each item
2. to Transpose rows starting from E till K


[TABLE="width: 378"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]New line added[/TD]
[TD][/TD]
[TD]Transposed Top header[/TD]
[TD]Transposed Item value[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple[/TD]
[TD] [/TD]
[TD]Mon[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple[/TD]
[TD] [/TD]
[TD]Tues[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple[/TD]
[TD] [/TD]
[TD]Wed[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple[/TD]
[TD] [/TD]
[TD]Thu[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple[/TD]
[TD] [/TD]
[TD]Fri[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple[/TD]
[TD] [/TD]
[TD]Sat[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apple[/TD]
[TD] [/TD]
[TD]Sun[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[TD]Mon[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[TD]Tues[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[TD]Wed[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[TD]Thu[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[TD]Fri[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[TD]Sat[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[TD]Sun[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Grapes[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Tomato[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Potato[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have 1000 records in the original file.


Please help.

Regards
1404

 

[alansidman]

If you have xl2016, then on the Data Tab, select Get & Transform.

Highlight your table, select From Table.

Highlight columns after the item and select Transform and then select Unpivot.

i you are using an earlier version of Excel, you can download Power query as an add in.

Here is the Mcode created by this action

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Mon", Int64.Type}, {"Tues", Int64.Type}, {"Wed", Int64.Type}, {"Thu", Int64.Type}, {"Fri", Int64.Type}, {"Sat", Int64.Type}, {"Sun", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

 

[excel1404]

Thank you Sir Alan.... It worked like a charm...Have Excel 2016...
As they say Known is a drop, Unknown is an Ocean...Thank you !!

Keep helping and keep inspiring !!

Regards
1404

If you have xl2016, then on the Data Tab, select Get & Transform.

Highlight your table, select From Table.

Highlight columns after the item and select Transform and then select Unpivot.

i you are using an earlier version of Excel, you can download Power query as an add in.

Here is the Mcode created by this action

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Mon", Int64.Type}, {"Tues", Int64.Type}, {"Wed", Int64.Type}, {"Thu", Int64.Type}, {"Fri", Int64.Type}, {"Sat", Int64.Type}, {"Sun", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

 

[alansidman]

Thanks for the feedback.