Link to home
Create AccountLog in
Avatar of Anne Smith
Anne Smith

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
Avatar of Anne Smith
Anne Smith

ASKER

Please find attached that has the data.
Sample.xlsx
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
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
Avatar of Professor J
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
Avatar of Anne Smith
Anne Smith

ASKER

Rob, thanks for your solution, but I am not allowed to do any steps manually.
Avatar of Anne Smith
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.
Avatar of Professor J
Professor J
Can you post a dummy example of your data so that I make a recording on that and share?
Avatar of Anne Smith
Anne Smith

ASKER

attached is the dummy data
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
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
Avatar of Professor J
Professor J
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Anne Smith
Anne Smith

ASKER

Thank you so much ProfessorJimJam for your help, it was very clear and has done the job.
Avatar of Professor J
Professor J
You are welcome Anne. Thanks for the feedback. I am glad I was able to help.