-1

I have several Excel spreadsheets with data layout like this raw data:

enter image description here

company company1    company2    company3
currency     $        Y          E
1/1/2013    32.68   12          3
1/2/2013    12.5    13          4
1/3/2013    45      45          8

which basically are time series data grouped together. I need the final layout transformed into panel data, like this wanted panel data:

enter image description here

Since my observations are usually very large, it is not practical manually to reformat it.

Is there a macro code that can achieve such a goal?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Xinxin Li
  • 51
  • 1
  • 1
  • 9

1 Answers1

4

Turn on Record Macro if desired. In Excel, move the currency row out of the way. 'Reverse pivot' (as detailed here), sort the Table on Column A to Z, switch the order of Columns B and C and fill Column D with a lookup of the company name against your currency indicators.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    As the currency is later needed for the `HLOOKUP`, instead of "moving it out of the way" it can be "filtered out" in the Pivot Table – EEM Nov 18 '15 at 21:36
  • Thanks to you, for the wizard and the reverse PivotTable concept. Learned something new today, great... – EEM Nov 18 '15 at 21:45
  • 1
    Thank you all for the suggestions! – Xinxin Li Nov 19 '15 at 18:54