Table transformation with PivotTable or Index Match

Question

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	5,447	10,792
IMPORTS	AUT	NA	NA	1,325
GDP	ARG	153	206	248
GDP	AUS	323	324	317
GDP	AUT	167	174	196

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	5,447	324
AUS	1992	10,792	317
AUT	1990	NA	167
AUT	1991	NA	174
AUT	1992	1,325	196

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!

sergeibaklan · Answer

You posted your question in two spaces, better to keep in one. Anyway, please clarify that&nbsp;https://techcommunity.microsoft.com/t5/Excel/Transform-table-format-with-multi-level-variables-maybe-with/m-p/210979#M7105

Forum Discussion

Table transformation with PivotTable or Index Match

Share

Resources