Home

Table transformation with PivotTable or Index Match

New Contributor

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!

1 Reply
Highlighted

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

Related Conversations
Counting Days
Tim Hunter in SQL Server on
2 Replies
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
Set Auto Refresh to Pivot Table as Source Data Changes
ratishkp in Excel on
2 Replies
Problem sorting Pivot Table according to date
MikkoS in Excel on
1 Replies
Matching Excel cells by column
ddelise in Excel on
3 Replies