Arranging panel data

Hi Bootje;

You can use Power Query for this transformation

1. Convert your Raw Data in Tables & then load it into Power Query Editor

2. When you convert it into tables uncheck my table has headers

Below are the step from the Power Query for the transformation. I will be attaching the solution file for your reference. 

let
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "company id", each if Text.StartsWith([Attribute.1], "company") then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"company id"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Attribute.1], "company")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"date", type date}, {"Attribute.1", type text}, {"Value", Currency.Type}, {"company id", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.1]), "Attribute.1", "Value", List.Sum),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"company id", Order.Ascending}, {"date", Order.Ascending}})
in
    #"Sorted Rows"

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more