Transform multi-level table format, maybe with pivot table?

[montaguelord]

I have an Excel 2016 file with a large set of panel data in the following actual form:
[TABLE="width: 261"]
<tbody>[TR]
[TD]VAR[/TD]
[TD]ISO[/TD]
[TD="colspan: 3, align: center"]VALUE[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1990[/TD]
[TD="align: center"]1991[/TD]
[TD="align: center"]1992[/TD]
[/TR]
[TR]
[TD]IMPORTS[/TD]
[TD] ARG[/TD]
[TD] 1,287 [/TD]
[TD] NA [/TD]
[TD] NA [/TD]
[/TR]
[TR]
[TD]IMPORTS[/TD]
[TD] AUS[/TD]
[TD] 9,178 [/TD]
[TD] 447 [/TD]
[TD] 10,792 [/TD]
[/TR]
[TR]
[TD]IMPORTS [/TD]
[TD] AUT[/TD]
[TD] NA [/TD]
[TD] NA [/TD]
[TD] 1,325 [/TD]
[/TR]
[TR]
[TD]GDP [/TD]
[TD] ARG[/TD]
[TD] 153 [/TD]
[TD] 206 [/TD]
[TD] 248 [/TD]
[/TR]
[TR]
[TD]GDP [/TD]
[TD] AUS[/TD]
[TD] 323 [/TD]
[TD] 324 [/TD]
[TD] 317 [/TD]
[/TR]
[TR]
[TD]GDP [/TD]
[TD] AUT[/TD]
[TD] 167 [/TD]
[TD] 174 [/TD]
[TD] 196 [/TD]
[/TR]
</tbody>[/TABLE]
I need to transform it to the following desired form:
[TABLE="width: 171"]
<tbody>[TR]
[TD]ISO[/TD]
[TD]YEAR[/TD]
[TD="colspan: 2"]VAR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"]IMPORTS[/TD]
[TD="align: right"]GDP[/TD]
[/TR]
[TR]
[TD="align: right"]ARG[/TD]
[TD="align: right"]1990[/TD]
[TD="align: right"]1,287 [/TD]
[TD="align: right"]153 [/TD]
[/TR]
[TR]
[TD="align: right"]ARG [/TD]
[TD="align: right"]1991[/TD]
[TD="align: right"] NA [/TD]
[TD="align: right"]206 [/TD]
[/TR]
[TR]
[TD="align: right"]ARG[/TD]
[TD="align: right"]1992[/TD]
[TD="align: right"] NA [/TD]
[TD="align: right"]248 [/TD]
[/TR]
[TR]
[TD="align: right"]AUS[/TD]
[TD="align: right"]1990[/TD]
[TD="align: right"]9,178 [/TD]
[TD="align: right"]323 [/TD]
[/TR]
[TR]
[TD="align: right"]AUS[/TD]
[TD="align: right"]1991[/TD]
[TD="align: right"] 447 [/TD]
[TD="align: right"]324 [/TD]
[/TR]
[TR]
[TD="align: right"]AUS[/TD]
[TD="align: right"]1992[/TD]
[TD="align: right"]10,792 [/TD]
[TD="align: right"]317 [/TD]
[/TR]
[TR]
[TD="align: right"]AUT[/TD]
[TD="align: right"]1990[/TD]
[TD="align: right"] NA [/TD]
[TD="align: right"]167 [/TD]
[/TR]
[TR]
[TD="align: right"]AUT[/TD]
[TD="align: right"]1991[/TD]
[TD="align: right"] NA [/TD]
[TD="align: right"]174 [/TD]
[/TR]
[TR]
[TD="align: right"]AUT[/TD]
[TD="align: right"]1992[/TD]
[TD="align: right"]1,325 [/TD]
[TD="align: right"]196 [/TD]
[/TR]
</tbody>[/TABLE]
Can anyone suggest a way to automatically transform the data from the actual format to the desired form:
I thought that a pivot table might work. But I don’t know how to do a multi-level pivot table that would have the ISO and YEAR in the same line.
Any suggestions would be grately appreciated!

 

[Joyner]

Hello and welcome to the board. I will assume you know pivot tables, if you are not sure of how to do something let me know. There may be other ways, but if you have Power Query, I would unpivot the data, then the rest is simple. to unpivot see this Mrexcel video:

https://www.youtube.com/watch?v=yX-QNxaOj9c

Note mrexel says go to the Power Query tab, depending on your version that is now on the Data ribbon under Get and Transform

After you unpivot the rest is a simple pivot table. Note though that the NAs will show as zeros, but you can change that to show NA, assuming you have no actual zeros in your data.

 

[montaguelord]

Great! Fantastic! Almost there.... I followed the video instructions and end up with the table below. There are 3 changes that I need to complete the transformation:
(1) most importantly, I need the ISO (country) label lined up with the Years (ARG 1990; ARG 1991;....AUT 1992)
(2) remove the last column (Grand Total)
(3) remove the rows showing the Grand Total for each ISO.
My 'Desired Table' above shows it better.
Here is what I get after the 'unpivot table with Power Query' and Pivot Table transformation:
[TABLE="width: 341"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Sum of Value[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]GDP[/TD]
[TD]IMPORTS[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]ARG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1990[/TD]
[TD="align: right"]153.205[/TD]
[TD="align: right"]1287[/TD]
[TD="align: right"]1440.205[/TD]
[/TR]
[TR]
[TD]1991[/TD]
[TD="align: right"]205.515[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]205.515[/TD]
[/TR]
[TR]
[TD]1992[/TD]
[TD="align: right"]247.987[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]247.987[/TD]
[/TR]
[TR]
[TD]ARG Total[/TD]
[TD="align: right"]606.707[/TD]
[TD="align: right"]1287[/TD]
[TD="align: right"]1893.707[/TD]
[/TR]
[TR]
[TD]AUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1990[/TD]
[TD="align: right"]323.21[/TD]
[TD="align: right"]9178[/TD]
[TD="align: right"]9501.21[/TD]
[/TR]
[TR]
[TD]1991[/TD]
[TD="align: right"]323.723[/TD]
[TD="align: right"]5447[/TD]
[TD="align: right"]5770.723[/TD]
[/TR]
[TR]
[TD]1992[/TD]
[TD="align: right"]317.479[/TD]
[TD="align: right"]10792[/TD]
[TD="align: right"]11109.479[/TD]
[/TR]
[TR]
[TD]AUS Total[/TD]
[TD="align: right"]964.412[/TD]
[TD="align: right"]25417[/TD]
[TD="align: right"]26381.412[/TD]
[/TR]
[TR]
[TD]AUT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1990[/TD]
[TD="align: right"]166.867[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]166.867[/TD]
[/TR]
[TR]
[TD]1991[/TD]
[TD="align: right"]174.435[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]174.435[/TD]
[/TR]
[TR]
[TD]1992[/TD]
[TD="align: right"]195.506[/TD]
[TD="align: right"]1325[/TD]
[TD="align: right"]1520.506[/TD]
[/TR]
[TR]
[TD]AUT Total[/TD]
[TD="align: right"]536.808[/TD]
[TD="align: right"]1325[/TD]
[TD="align: right"]1861.808[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]2107.927[/TD]
[TD="align: right"]28029[/TD]
[TD="align: right"]30136.927[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joyner]

Great, so when you are clicked in your PT, go to the Design contextual ribbon, all the way to the left, find Report Layout, select show in Tabular, and also select Repeat allitem labels, Go to Subtotals and selectdo not show subtotal, and Grand Total, do not show Grand Totals, that shouldshow what you want.

 

[montaguelord]

Excellent! Solved. Thank you both for your quick replies and for the clear instructions.
One final query, since my data set is so large and I'm trying to reduce the work:
Below is the result of your instructions, which is fine. But would it be possible to somehow get the ISO names to repeat for each year, rather than for just the first year. Not a problem, since I can always copy down. But if there's a way to automate the process, that would be great.

[TABLE="width: 260"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Sum of Value[/TD]
[TD][/TD]
[TD]VAR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ISO[/TD]
[TD]Year[/TD]
[TD]GDP[/TD]
[TD]IMPORTS[/TD]
[/TR]
[TR]
[TD]ARG[/TD]
[TD]1990[/TD]
[TD="align: right"]153.205[/TD]
[TD="align: right"]1287[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1991[/TD]
[TD="align: right"]205.515[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1992[/TD]
[TD="align: right"]247.987[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AUS[/TD]
[TD]1990[/TD]
[TD="align: right"]323.21[/TD]
[TD="align: right"]9178[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1991[/TD]
[TD="align: right"]323.723[/TD]
[TD="align: right"]5447[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1992[/TD]
[TD="align: right"]317.479[/TD]
[TD="align: right"]10792[/TD]
[/TR]
[TR]
[TD]AUT[/TD]
[TD]1990[/TD]
[TD="align: right"]166.867[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1991[/TD]
[TD="align: right"]174.435[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1992[/TD]
[TD="align: right"]195.506[/TD]
[TD="align: right"]1325[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joyner]

Sure, I think you missed this one step: under Report Layout... also select "Repeat All Item Labels"

 

[montaguelord]

Excellent, that worked. I guess it didn't work the first time because I clicked on Repeat all item labels right after I selected show in Tabular. It appears that those two steps need to be done separately.
Many thanks for your extremely helpful and timely support!

 

[Joyner]

Thanks, you are welcome