We help IT Professionals succeed at work.

Unpivot nested excel tables

Anne Smith
Anne Smith asked
on
14 Comments
1 Solution
137 Views
Last Modified: 2017-05-16
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
Comment
Watch Question

View Solution Only

Anne Smith

Author

Commented:
Please find attached that has the data.
Sample.xlsx
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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 HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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 JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
@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

Author

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

Author

Commented:
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 JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

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

Author

Commented:
attached is the dummy data
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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.
Microsoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Anne Smith

Author

Commented:
Thank you so much ProfessorJimJam for your help, it was very clear and has done the job.
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
You are welcome Anne. Thanks for the feedback. I am glad I was able to help.
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.

View Solution