I have data stored as an excel file taken from the U.S. Energy Information Administration. The below is a snippet of that data. I want to transfer this to a GIS-usable format. As such, I want to combine the data in the MSN column by code with the data in the year columns. Take the image below as the current data:
I want to have a resulting table that looks like this:
Edit: I've semi-solved this with just a pivot table. I created a pivot table with the MSN codes as Columns, the StateCodes as rows and the year data as a Sum value. If I was only manipulating data in excel, that'd be good enough. The MSN codes are an overall title with each yearly sum a column/title underneath that. Is there a way to combine the overall title with each individual column so that the output resembles the above second image?