Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

We are trying to create a pivot table and combine two separate workbooks, that do not have the same data in the same cells. Is this even possible? For example the data is sales on items by month, one workbook has the sales broke down by month, the other work book has sales broke down by week. We can convert the data, before hand, but we are trying to forecast sales of times.
Solved by T. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 09/08/2018 - 10:25
Hi welcome!
User 09/08/2018 - 10:27
Hi there
Excelchat Expert 09/08/2018 - 10:27
You can consolidate sheets in a single pivot, but will need to convert time frames to a consistent format , if expected to use a time reference in the pivot
User 09/08/2018 - 10:29
So it is possible as long as all the data on each Workbook matches exactly
Excelchat Expert 09/08/2018 - 10:29
Not the data necessarily, but the structure (columns and row) format
Excelchat Expert 09/08/2018 - 10:30
Setting up the source data Each range of data should be arranged in cross-tab format, with matching row and column names for items that you want to summarize together.
User 09/08/2018 - 10:30
Can I send you a copy of the data that we are trying to attempt to do
Excelchat Expert 09/08/2018 - 10:30
User 09/08/2018 - 10:33
This is the data we get from amazon
[Uploaded an Excel file]
User 09/08/2018 - 10:34
This is the data we need to merg into the excel data from amazon
[Uploaded an Excel file]
Excelchat Expert 09/08/2018 - 10:35
Ok, so not different sheets , nut different workbooks
Excelchat Expert 09/08/2018 - 10:35
Let me take a look
User 09/08/2018 - 10:36
You are my hero if you can figure it out , Im on hour 7 of trying to make this work
Excelchat Expert 09/08/2018 - 10:38
have both open, but not folowing the data
Excelchat Expert 09/08/2018 - 10:38
What sheets and columns need to be consolidated?
Excelchat Expert 09/08/2018 - 10:39
I see simple AMAZON forecast by month, what data from LODGE is to be aligned?
User 09/08/2018 - 10:40
The data from Amazon is gathered weekly, and then we need to take that and convert it to lodges monthly forecast form
Excelchat Expert 09/08/2018 - 10:41
Amazon appears to be monthly?
Excelchat Expert 09/08/2018 - 10:41
Excelchat Expert 09/08/2018 - 10:42
And this data is to be loaded into the TO COMPLETE
Excelchat Expert 09/08/2018 - 10:42
sheet in LODGE?
User 09/08/2018 - 10:43
I'm sorry yes its already converted, but we need to be able to impute the monthly totals into the lodge form automatically each month , with out having to sum up each weeks of sales
User 09/08/2018 - 10:44
all the items need to match the correct item number
Excelchat Expert 09/08/2018 - 10:44
I see the item numbers are unique , so a lookup should work fine
User 09/08/2018 - 10:45
Like a Vlook up ?
User 09/08/2018 - 10:45
another issue is not all item numbers match perfectly there are some on the amazon that are not on the lodge form
Excelchat Expert 09/08/2018 - 10:45
Yes, likley a combo H and V lookup (month and item#) , or INDEX/MATCH
User 09/08/2018 - 10:46
how much would you charge to create a template for us to use and teach us how to use it?
Excelchat Expert 09/08/2018 - 10:46
Sorry, I can't do that here
Excelchat Expert 09/08/2018 - 10:47
This is just a simple formula help site, not solujtion develpoment.
Excelchat Expert 09/08/2018 - 10:47
Do you have an example of the AMAZON sheet before you converted?
Excelchat Expert 09/08/2018 - 10:48
I may be able to help somewhat, but I think a conversion template, that matches the LODGE format, then PIVOT is a way to go, since you may have items in one file and not the other.
User 09/08/2018 - 10:49
let me find the raw data from amazon
Excelchat Expert 09/08/2018 - 10:49
The consolidation and PIVOT is a type of "Outer join" that gets the unique items from both sources
User 09/08/2018 - 10:51
Here is the raw data from amazon
[Uploaded an Excel file]
User 09/08/2018 - 10:51
We only use the P70
Excelchat Expert 09/08/2018 - 10:52
Cell P70?
User 09/08/2018 - 10:53
Only the columns labeled P70
User 09/08/2018 - 10:54
Sorry cell AQ to BP
User 09/08/2018 - 10:56
We have been hand entering all that data by hand
Excelchat Expert 09/08/2018 - 10:56
You mean you added weeks and hand entered into a month column?
User 09/08/2018 - 10:56
yes we do
Excelchat Expert 09/08/2018 - 10:57
yes, we need to help you with that
Excelchat Expert 09/08/2018 - 10:58
What descriptive info is needed?
Excelchat Expert 09/08/2018 - 10:58
ASIN Product Title
Excelchat Expert 09/08/2018 - 10:58
anything else?
User 09/08/2018 - 10:58
model number
User 09/08/2018 - 10:59
these numbers are total units of each item sold
Excelchat Expert 09/08/2018 - 10:59
I dont see model number?
User 09/08/2018 - 11:00
We manual add that part too to each ASIN
Excelchat Expert 09/08/2018 - 11:01
If that is pre-defined , should be able to set that up with a LOOKUP or INDEX/MATch
User 09/08/2018 - 11:01
We have to match them up , since Amazon data comes over with more data than we need, so we first go in and add the model numbers manually then go back and total the months then manually ender line by line into the Lodge form
User 09/08/2018 - 11:03
Here is what is looks like after we added the model number
[Uploaded an Excel file]
Excelchat Expert 09/08/2018 - 11:03
But can every AMAZON ASIN number be pre-defined to a model number? Lookup in a table
User 09/08/2018 - 11:03
Excelchat Expert 09/08/2018 - 11:04
Well I can help with the lookup and will see how best to convert the week data.
Excelchat Expert 09/08/2018 - 11:05
but only 19 minutes left, let me analyze a bit and then give some recommendations.
Excelchat Expert 09/08/2018 - 11:05
Please stand by
Excelchat Expert 09/08/2018 - 11:20
[Uploaded an Excel file]
Excelchat Expert 09/08/2018 - 11:20
We dont have much time . but I set up a simple template design.
Excelchat Expert 09/08/2018 - 11:21
It requires some setup effort , but do it once and wont be much going forward
User 09/08/2018 - 11:21
I really appreciate your help, you are amazing
Excelchat Expert 09/08/2018 - 11:21
I did build in the LOOKUP on this simple, small example
Excelchat Expert 09/08/2018 - 11:21
The setup is to map weeks and create month totals in the template.
Excelchat Expert 09/08/2018 - 11:22
You should know which weeks = month
Excelchat Expert 09/08/2018 - 11:22
make sure you referfence correctly back to the AMAZON source.
Excelchat Expert 09/08/2018 - 11:22
=+'Amazon DATA'!C2, basic + formula to align weeks
Excelchat Expert 09/08/2018 - 11:23
Use the design template as a guide to build out each week to month total.
Excelchat Expert 09/08/2018 - 11:23
This will save you some time
User 09/08/2018 - 11:24
I'm sure I can figure out the vlook up if I had a example
Excelchat Expert 09/08/2018 - 11:24
Please stop back if you need help
User 09/08/2018 - 11:24
Thank you so much
Excelchat Expert 09/08/2018 - 11:24
User 09/08/2018 - 11:24
I will for shore use this service

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc