troubleshooting Question

Unpivot nested excel tables

Avatar of Anne Smith
Anne Smith asked on
Microsoft OfficeMicrosoft Excel
14 Comments1 Solution148 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 14 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros