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
yes
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
AUG SEP OCT NOV
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
yes
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
bye
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.
