asked on

Unpivot nested excel tables

Hi,

I have an excel file with below pivot table:
                   2016             2015                   2014
       longitude      latitude      jan      feb      march      jan      feb      march      jan      feb      march
city1      12.34      20.1      2      3      4      56      14      22      33      11      10 30 21 11
city2      13.98      40.2      4      5      6      9      12      11      10      12      18 17 18 10
.
.
.

and would like to flatten to:

      longitude      latitude      year      month      value
city1      12.34      20.1        2016        jan        2
city2      13.98      40.2        2016        jan        4
city1      12.34      20.1        2016        feb        3
city2      13.98      40.2        2016        feb        5
city1      12.34      20.1        2016        march      4
city2      13.98      40.2        2016        march      6
city1      12.34      20.1 2015        jan        56
city2      13.98      40.2        2015        jan        9

and so on.

Can anyone help.

Thanks

Anne Smith

ASKER

Please find attached that has the data.
Sample.xlsx

Rob Henson

See attached with the following formulae:

Lat & Long
=INDEX($A$1:$L$4,MATCH($A22,$A:$A,0),MATCH(B$21,$2:$2,0))

Year and Month filled in manually
Values
=INDEX($A$1:$L$4,MATCH($A22,$A:$A,0),MATCH($D22,$1:$1,0)+MATCH($E22,$2:$2,0)-4)
Flattened-data.xlsx

Rob Henson

If the source table was indeed a Pivot Table, the solution would be much simpler. Remove all filters, ensure rows and columns have totals and then double click the Grand Total at the bottom right. This will reproduce the source data in its entirety.

Thanks
Rob H

Professor J

@Anne Smith

i personally do not think you can find a better solution other than using PowerQuery also called (Get & Transform).

it is available for Excel 2010 and 2013 version to download from https://www.microsoft.com/en-us/download/details.aspx?id=39379

and if you have office 356 or Excel 2016, it is already built in, so no need to download.

i have recorded an exclusive video for you on how you can easily achieve the needed result by using powerquery.

please watch the video i uploaded in youtube https://www.youtube.com/watch?v=S1o0GyIONmM

i have also attached the exercise file you can practice by yourself.
Transform-Nested-Table-to-Pivot-Tab.xlsx

Anne Smith

ASKER

Rob, thanks for your solution, but I am not allowed to do any steps manually.

Anne Smith

ASKER

Professor JimJam,

I have tried it but got stuck, the issue was that I am not merging any columns, that were I stopped.

Can you give a quick guide on how to do it on my data.

Thanks.

Professor J

Can you post a dummy example of your data so that I make a recording on that and share?

Anne Smith

ASKER

attached is the dummy data

Anne Smith

ASKER

Sample.xlsx

Rob Henson

Not allowed to do any steps manually.

So I assume this is a homework assignment, if it were work you could make up your own mind whether to do parts manually.

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anne Smith

ASKER

Thank you so much ProfessorJimJam for your help, it was very clear and has done the job.

Professor J

You are welcome Anne. Thanks for the feedback. I am glad I was able to help.

Professor J