Solved

Convert Pivot Table Data

Posted on 2015-02-11
2
99 Views
Last Modified: 2015-02-11
I have a worksheet that contains the columns: Person ID code, Name, Location. Each person has at least one location, but many have multiple locations. People with multiple locations have their ID code & name duplicated in subsequent rows, The different location is then stored. For example:

ID Code Name         Location
12345    Bloggs, Joe London
12345    Bloggs, Joe Manchester
67890    Smith, Jim  Manchester
67890    Smith, Jim  Southampton

What I want to do is convert the layout so it is :

ID Code Name           Location1      Location2            Location3
12345    Bloggs, Joe   London          Manchester
67890    Smith, Jim    Manchester   Southampton

I have created a pivot table which has grouped the people with their locations easily enough. But what I cannot figure out next is how to export the pivot table so that each element in the pivot table report occupies one row in a new worksheet.
0
Comment
Question by:Paul Gardner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40603023
You can't - directly. You can do it in SQL Server PIVOT, but not in Excel. It's for counting/summing/averaging numbers, not text.

Therefore, you need to do a lookup - see the attached.
EE150211.xlsx
0
 

Author Closing Comment

by:Paul Gardner
ID: 40603344
Thank for your reply, that has worked perfectly. It took a while to step through the various formulas to realise how it all works. But I think I understand it now, so the challenge will be able to recreate it next time I have a similar requirement.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question