Question description:
This user has given permission to use the problem statement for this
blog.
I have an existing Pivot Table of Sales Data. Item ID is a filter, but not visible in my table. I have a separate workbook with Item ID, Class A, Class B. I want to "unpivot" VLOOKUP to get Class A & Class B columns into Table Data (by Item ID), and then recreate the Pivot Table.
Solved by M. J. in 26 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
15/10/2018 - 07:27
Hi Welcome to excelchat
User
15/10/2018 - 07:27
Hello.
Excelchat Expert
15/10/2018 - 07:27
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows
Excelchat Expert
15/10/2018 - 07:27
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User
15/10/2018 - 07:27
OK
Excelchat Expert
15/10/2018 - 07:27
Could you please share the file?
User
15/10/2018 - 07:28
sure.
Excelchat Expert
15/10/2018 - 07:28
thank you
User
15/10/2018 - 07:28
I have 2 file. One with Sales Data and Sage 50 Pivot Table, I am trying to merge with #2
Excelchat Expert
15/10/2018 - 07:28
okay
User
15/10/2018 - 07:29
[Uploaded an Excel file]
User
15/10/2018 - 07:30
[Uploaded an Excel file]
Excelchat Expert
15/10/2018 - 07:30
opening them
Excelchat Expert
15/10/2018 - 07:33
Are the files big in size, they are taking a lot of time to download
User
15/10/2018 - 07:33
First is 3.7M
User
15/10/2018 - 07:34
2nd is 116K
Excelchat Expert
15/10/2018 - 07:34
Second file downloaded
Excelchat Expert
15/10/2018 - 07:34
but first is still downloading
Excelchat Expert
15/10/2018 - 07:35
while the file is downloading, could you please explain about the problem
User
15/10/2018 - 07:36
OK.
User
15/10/2018 - 07:36
This is a Sage 50 Sales analysis Excel Pivot Table.
User
15/10/2018 - 07:37
filters are Item ID, Item Description, Customer ID, Sales Rep, & Transaction Type.
Excelchat Expert
15/10/2018 - 07:38
ok, the file is downloaded
User
15/10/2018 - 07:38
columns are customer Name, Line Description, then several Calulated fields
Excelchat Expert
15/10/2018 - 07:38
okay
Excelchat Expert
15/10/2018 - 07:40
What do you want me to do?
User
15/10/2018 - 07:41
How do I unpivot the table?
User
15/10/2018 - 07:42
Then I want to VLOOKUP the fields Primary & Secondary, by ItemID, and then repivot?
User
15/10/2018 - 07:42
!!
Excelchat Expert
15/10/2018 - 07:42
okay, go tit
Excelchat Expert
15/10/2018 - 07:43
The main idea behind unpivot is to get the filter Item ID in the data, right?
User
15/10/2018 - 07:43
Correct.
User
15/10/2018 - 07:45
I have a second screen and can follow along.
Excelchat Expert
15/10/2018 - 07:46
So, the data of the pivot table is in sheet1?
User
15/10/2018 - 07:46
I don't know...that is the basis of my challenge.
User
15/10/2018 - 07:46
I don't see a sheet1.
Excelchat Expert
15/10/2018 - 07:47
A sheet with sheet1 name is hidden
Excelchat Expert
15/10/2018 - 07:47
With unpivot, you can't get the output desired
Excelchat Expert
15/10/2018 - 07:47
Unpivot is used to transpose the data
User
15/10/2018 - 07:48
hidden excel sheet?
Excelchat Expert
15/10/2018 - 07:48
yes
Excelchat Expert
15/10/2018 - 07:48
If you right-click on any sheet name and select unhide
Excelchat Expert
15/10/2018 - 07:49
you will see the hidden sheets
Excelchat Expert
15/10/2018 - 07:49
As I was saying, unpivot is not an option here
User
15/10/2018 - 07:50
Awesome!!
User
15/10/2018 - 07:50
OK.
Excelchat Expert
15/10/2018 - 07:50
For to accomplish the outcome, we need the original data
Excelchat Expert
15/10/2018 - 07:50
So, what do you want me to do?
User
15/10/2018 - 07:51
I see Sheet1 now
Excelchat Expert
15/10/2018 - 07:51
great
User
15/10/2018 - 07:52
If I add the columns I want, I should be able to create a new pivot table.
Excelchat Expert
15/10/2018 - 07:52
yes
User
15/10/2018 - 07:52
I've learned so much today already. I think you've got me feeling better.
User
15/10/2018 - 07:53
I'll see what I can do myself now. Thank you for your support.
Excelchat Expert
15/10/2018 - 07:53
you are welcome
User
15/10/2018 - 07:53
Have a great Day!!
Excelchat Expert
15/10/2018 - 07:53
you too
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.