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:

VAR	ISO	VALUE
		1990	1991	1992
IMPORTS	ARG	1,287	NA	NA
IMPORTS	AUS	9,178	447	10,792
IMPORTS	AUT	NA	NA	1,325
GDP	ARG	153	206	248
GDP	AUS	323	324	317
GDP	AUT	167	174	196

<tbody>
</tbody>

I need to transform it to the following desired form:

ISO	YEAR	VAR
		IMPORTS	GDP
ARG	1990	1,287	153
ARG	1991	NA	206
ARG	1992	NA	248
AUS	1990	9,178	323
AUS	1991	447	324
AUS	1992	10,792	317
AUT	1990	NA	167
AUT	1991	NA	174
AUT	1992	1,325	196

<tbody>
</tbody>

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:

Sum of Value	Column Labels
Row Labels	GDP	IMPORTS	Grand Total
ARG
1990	153.205	1287	1440.205
1991	205.515	0	205.515
1992	247.987	0	247.987
ARG Total	606.707	1287	1893.707
AUS
1990	323.21	9178	9501.21
1991	323.723	5447	5770.723
1992	317.479	10792	11109.479
AUS Total	964.412	25417	26381.412
AUT
1990	166.867	0	166.867
1991	174.435	0	174.435
1992	195.506	1325	1520.506
AUT Total	536.808	1325	1861.808
Grand Total	2107.927	28029	30136.927

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.

Sum of Value		VAR
ISO	Year	GDP	IMPORTS
ARG	1990	153.205	1287
	1991	205.515	0
	1992	247.987	0
AUS	1990	323.21	9178
	1991	323.723	5447
	1992	317.479	10792
AUT	1990	166.867	0
	1991	174.435	0
	1992	195.506	1325

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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