0

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:

enter image description here

I want to have a resulting table that looks like this:

enter image description here

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?

5
  • You are looking for UnPivot or Flatten: stackoverflow.com/questions/20541905/… Aug 23 '19 at 13:41
  • 2
    Possible duplicate of How to "unpivot" or "reverse pivot" in Excel? Aug 23 '19 at 13:50
  • I'm looking through unpivot tutorials and that doesn't seem to quite..fit. There's the additional complication in wanting to create crosstab data based on a third variable. I'll edit the above to try and be more clear. Aug 23 '19 at 15:39
  • I think unpivot is just the start. After you would create a new column concatenate the row and column headers. Then you would repivot and you should get your desired format.
    – gns100
    Aug 24 '19 at 0:22
  • How about a link to the source. Your example, even if it could be pasted into a worksheet, does not have the information required to produce your desired results. Aug 24 '19 at 18:51
0

You should be able to do this with Power Query aka Get & Transform

enter image description here

with the cursor in the original table

  • Data --> Get & Transform Data --> From Table/Range
  • The Power Query Editor should open
    • Select the MSN and StateCode columns
    • Unpivot other columns

enter image description here

  • Select the Attribute column and replace Y with _
  • Merge the MSN and Attribute columns
  • Sort the Merged column or the StateCode if you like
  • Select the Merged column and Pivot, with the Values column being Value, and the aggregate function being Sum

    Close and Load

enter image description here

0

Assuming I've read this correctly and you want another 4 columns to the right, but with combined values, like this:

enter image description here

Let's call the current columns A to F (as in the picture)

In cell G1 enter the formula =$B1&C1

Copy this across the next 3 columns, and the copy all 4 columns down to the bottom of the data

3
  • Meh I dunno, I've reread the question and I don't think it's this actually. Let me know and I'll just delete this answer.
    – Smock
    Aug 23 '19 at 14:06
  • Not quite! I want to append data in one row to the data in multiple columns to produce new columns. Basically having CLPR1990, CLPR2000, CLPRK2010, etc. The only row would be by the state name, AK, AL, AR, etc. Aug 23 '19 at 15:45
  • 1
    Perhaps you could put something into excel manually and show us a screenshot of how it should look?
    – Smock
    Aug 23 '19 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.