VBA Copy Past Data

[unknownymous]

Hi Gurus,

Could possibly help me formulate the codes so I can get the results like the Clean Sheet?

I just need to get the data per account per month in descending order and will exclude those accounts with zero shares.

Raw sheet

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Name[/TD]
[TD]Note[/TD]
[TD]Mar-19[/TD]
[TD]Feb-19[/TD]
[TD]Jan-19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]80[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD]160[/TD]
[TD]140[/TD]
[TD]70[/TD]
[/TR]
</tbody>[/TABLE]

Clean Sheet

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Name[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]03/30/2019[/TD]
[TD]001[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]03/30/2019[/TD]
[TD]002[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]03/30/2019[/TD]
[TD]003[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]02/28/2019[/TD]
[TD]001[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]02/28/2019[/TD]
[TD]002[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]02/28/2019[/TD]
[TD]003[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]01/31/2019[/TD]
[TD]001[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]01/31/2019[/TD]
[TD]002[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help will be much appreciated.

 

[alansidman]

Quickly accomplished using Power Query. Mcode below. Unpivot your data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", Int64.Type}, {"Name", type any}, {"Note", type any}, {"19-Mar", Int64.Type}, {"19-Feb", Int64.Type}, {"19-Jan", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Name", "Note"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if([Attribute]="Account") then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute] <> "Account")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Attribute", "Custom", "Value"})
in
    #"Reordered Columns"